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

Answer 1

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/

Answer 2

 

Many thanks Jonathon

That did the trick.

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

Cheers

Answer 3

Can this also be utilised using visual studio 6.0?

Answer 4

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/

Answer 5

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 

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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 

Answer 10

imports Excel