locked
can't import Excel into Visual Basic

    Question

  • Hi Hope some body can help with this: -

    I have just installed Visual Studio 2005 Professional onto my PC and want to write an application that opens an Excel spreadsheet and reads data from a specific range in the sheet.

    Problem is I cannot seem to import the Excel objects into the program. Looking around on the net I have found some code like this: -

    Option Strict On

    Imports System.Windows.Forms

    Imports Excel = Microsoft.Office.Interop.Excel

    Imports Office = Microsoft.Office.Core

    Imports System.Drawing

    However this just produces an error to the effect that the office and excel lines are not recognised.

    Can anyone tell me what the correct import would be to enable me to use the excel objects?

    Many thanks

    Tuesday, August 22, 2006 6:33 PM

Answers

  • That's a good start, but you also need to add a reference to "Microsoft Excel 11.0 Object Library."  Once you've added the reference, you can use the code in this article to get you started:

    http://support.microsoft.com/kb/302094/

    Tuesday, August 22, 2006 10:51 PM
    Moderator

All replies

  • That's a good start, but you also need to add a reference to "Microsoft Excel 11.0 Object Library."  Once you've added the reference, you can use the code in this article to get you started:

    http://support.microsoft.com/kb/302094/

    Tuesday, August 22, 2006 10:51 PM
    Moderator
  •  

    Many thanks Jonathon

    That did the trick.

    Tried flagging this as the correct answer but got a script error.

    Cheers

    Wednesday, August 23, 2006 10:18 AM
  • Can this also be utilised using visual studio 6.0?
    Friday, September 08, 2006 9:26 AM

  • Yes, there is a link at the bottom of the Visual Basic.NET article for Visual Basic 6.0:

    http://support.microsoft.com/kb/247412/EN-US/

    Friday, September 08, 2006 12:48 PM
  • Hi,

                          I also have the same requirement , that is populate an excel spread-sheet through visual basic , but the same problem persist for me also , I tried the link Jonathan Aneja have provided , but it's not working as of now. So can anybody kindly provide me the details required to add a reference to "Microsoft Excel 11.0 Object Library".

                                                                   Thanks In Advance,

                                                                                                                 Regards,

                                                                                                                   Lokesh 

    Wednesday, September 27, 2006 12:35 PM
  • Hi Lokesh

    I don't have VS open in front of me but from memory you select 'Add Reference' on the Project menu.

    Hope this helps

    Bradley

    Wednesday, September 27, 2006 4:29 PM
  • Hi Bradley,

                 Thanks for your comment and I was able to add reference for "Microsoft Excel 11.0 Object Library" , but even after adding a reference , I was able to see only "Microsoft.Office.Core"  and "Microsoft.Office.Interop" was not present. So can you kindly help me out getting this done.

                                                           Thank you,

                                                                                                  Regards,

                                                                                                        Lokesh

    Thursday, September 28, 2006 5:40 AM
  • Hi Lokesh

    I just ran this in VB express and it worked so give it a try: -

    Create a form with a button and a checkbox called FillWithStrings

    Then add this code to the form: -

    Imports System.Windows.Forms

    Imports Offce = Microsoft.Office.Core

    Imports System.Drawing

    Public Class Form1

    Dim objApp As Excel.Application

    Dim objBook As Excel._Workbook

    Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim objBooks As Excel.Workbooks

    Dim objSheets As Excel.Sheets

    Dim objSheet As Excel._Worksheet

    Dim range As Excel.Range

    ' Create a new instance of Excel and start a new workbook.

    objApp = New Excel.Application()

    objBooks = objApp.Workbooks

    objBook = objBooks.Add

    objSheets = objBook.Worksheets

    objSheet = objSheets(1)

    'Get the range where the starting cell has the address

    'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.

    range = objSheet.Range("A1", Reflection.Missing.Value)

    range = range.Resize(5, 5)

    If (Me.FillWithStrings.Checked = False) Then

    'Create an array.

    Dim saRet(5, 5) As Double

    'Fill the array.

    Dim iRow As Long

    Dim iCol As Long

    For iRow = 0 To 5

    For iCol = 0 To 5

    'Put a counter in the cell.

    saRet(iRow, iCol) = iRow * iCol

    Next iCol

    Next iRow

    'Set the range value to the array.

    range.Value = saRet

    Else

    'Create an array.

    Dim saRet(5, 5) As String

    'Fill the array.

    Dim iRow As Long

    Dim iCol As Long

    For iRow = 0 To 5

    For iCol = 0 To 5

    'Put the row and column address in the cell.

    saRet(iRow, iCol) = iRow.ToString() + "|" + iCol.ToString()

    Next iCol

    Next iRow

    'Set the range value to the array.

    range.Value = saRet

    End If

    'Return control of Excel to the user.

    objApp.Visible = True

    objApp.UserControl = True

    'Clean up a little.

    range = Nothing

    objSheet = Nothing

    objSheets = Nothing

    objBooks = Nothing

    End Sub

     End Class

    Hope it helps

    Bradley

    Tuesday, October 03, 2006 5:13 PM
  • Hi Bradley,

                This works fine , thanks for your kind reply and the query I have raised in my last post was the Microsoft.Office.Core is getting imported but Microsoft.Office.Interop interface was not even present ( I'm using Visual Studio 2005 Proffessional Edition ) . Anyway I was able to accomplish the requirement with the code you have given , again thanks a lot for your response.

    Thanks & Regards,

       Lokesh R 

    Wednesday, October 04, 2006 12:51 PM
  • imports Excel

    Saturday, May 26, 2007 10:05 AM