How to read data through Excel in VB.net

[candice]

hello pals:
I now encounter a problem. I wrote a procedure before to read datas from Excel in VB6.

Private Sub Form_Load()
  Dim X1 As Integer
  Dim X2 As Integer
  Dim Y1 As Integer
  Dim Y2 As Integer

Dim oXLApp As Excel.Application         'Declare the object variables
Dim oXLBook As Excel.Workbook
Dim oXLSheet As Excel.Worksheet
  Set oXLApp = New Excel.Application    'Create a new instance of Excel
  Set oXLBook = oXLApp.Workbooks.Open("D:\New Folder\Book1") 'Open an existing workbook
  Set oXLSheet = oXLBook.Worksheets(1)  'Work with the first worksheet
  
Dim my_variable As String
my_variable = "hello"
oXLSheet.Cells(2, 1).Value = my_variable
X1 = oXLSheet.Cells(1, 1).Value
Y1 = oXLSheet.Cells(1, 2).Value
X2 = oXLSheet.Cells(1, 3).Value
Y2 = oXLSheet.Cells(1, 4).Value
oXLApp.Visible = True                'Show it to the user
  Set oXLSheet = Nothing               'Disconnect from all Excel objects (let the user take over)
  Set oXLBook = Nothing
  Set oXLApp = Nothing

End Sub

But when I run it in vb.net. It shows that
Excel.Application
Excel.Workbook
Excel.Worksheet
are not defined. Can anybody tell me that what's the equal definition in VB.net for the above three statement?

Any hint will be very appriciated!

 

[CygNuS]

Right-click your project and Add reference
in tab COM, check Microsoft Excel (11.0) Object Library

In your form code, at the very top, add
Imports Microsoft.Office.interop

This should get rid of the syntax errors

 

[candice]

Thanks very much CygNus!
Yes I fogot to add reference... stupid...
And now my code are like this

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] Microsoft.Office.Interop.Excel
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Form1
 
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] X1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] X2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Y1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Y2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
 
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Form_Load()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oXLApp [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Application [/SIZE][SIZE=2][COLOR=#008000]'Declare the object variables
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oXLBook [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Workbooks
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oXLSheet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Worksheet
oXLApp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Application() [/SIZE][SIZE=2][COLOR=#008000]'Create a new instance of Excel
[/COLOR][/SIZE][SIZE=2]oXLBook = oXLApp.Workbooks.Open([/SIZE][SIZE=2][COLOR=#800000]"C:\Documents and Settings\intern\My Documents\Book1"[/COLOR][/SIZE][SIZE=2]) [/SIZE][SIZE=2][COLOR=#008000]'Open an existing workbook
[/COLOR][/SIZE][SIZE=2]oXLSheet = oXLBook.Worksheets(1) [/SIZE][SIZE=2][COLOR=#008000]'Work with the first worksheet
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] my_variable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]my_variable = [/SIZE][SIZE=2][COLOR=#800000]"hello"
[/COLOR][/SIZE][SIZE=2]oXLSheet.Cells(2, 1).Value = my_variable
X1 = oXLSheet.Cells(1, 1).Value
Y1 = oXLSheet.Cells(1, 2).Value
X2 = oXLSheet.Cells(1, 3).Value
Y2 = oXLSheet.Cells(1, 4).Value
oXLApp.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#008000]'Show it to the user
[/COLOR][/SIZE][SIZE=2]oXLSheet = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#008000]'Disconnect from all Excel objects (let the user take over)
[/COLOR][/SIZE][SIZE=2]oXLBook = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2]oXLApp = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE]

The error appears in the line

Set oXLSheet = oXLBook.Worksheets(1) 'Work with the first worksheet

is that "worksheets" is not a member of "Microsoft.Office.Interop.Excel.Workbooks" ,what's that mean?

 

[CygNuS]

Dim oXLBook As Workbooks
-> shouldn't this be without the 's' : Dim oXLBook As Workbook ?
I got COM errors when otherwise... and i had absolutely no clue why until i saw the extra 's'

About your problem: change the line of code you have to:

oXLSheet = oXLBook.Application.Worksheets.Item(1) 'Work with the first worksheet
oXLSheet.Activate() 'Activates that worksheet (might be necessary if it's not first sheet)

 

[candice]

Thanks CygNuS,
Yes I delete the 's', and now there is no error in the code, but still it can't run as I expect. The excel file I want to show didn't come out. Also, there are no data reading and writing excuted.

[SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] Microsoft.Office.Interop.Excel
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Runtime.InteropServices
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Form1
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] X1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] X2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Y1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Y2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Form_Load()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oXLApp [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Application [/SIZE][SIZE=2][COLOR=#008000]'Declare the object variables
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oXLBook [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Workbook
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] oXLSheet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Worksheet
oXLApp = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Application() [/SIZE][SIZE=2][COLOR=#008000]'Create a new instance of Excel
[/COLOR][/SIZE][SIZE=2]oXLBook = oXLApp.Workbooks.Open([/SIZE][SIZE=2][COLOR=#800000]"C:\Documents and Settings\intern\My Documents\Book1"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]'Open an existing workbook
[/COLOR][/SIZE][SIZE=2]oXLBook.Activate()
oXLSheet = oXLBook.Application.Worksheets.Item(1) [/SIZE][SIZE=2][COLOR=#008000]'Work with the first worksheet
[/COLOR][/SIZE][SIZE=2]oXLSheet.Activate() [/SIZE][SIZE=2][COLOR=#008000]'Activates that worksheet (might be necessary if it's not first sheet)
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] my_variable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]my_variable = [/SIZE][SIZE=2][COLOR=#800000]"hello"
[/COLOR][/SIZE][SIZE=2]oXLSheet.Cells(2, 1).Value = my_variable
X1 = oXLSheet.Cells.Item(1, 1).Value
Y1 = oXLSheet.Cells.Item(1, 2).Value
X2 = oXLSheet.Cells.Item(1, 3).Value
Y2 = oXLSheet.Cells.Item(1, 4).Value
oXLApp.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#008000]'Show it to the user
[/COLOR][/SIZE][SIZE=2]oXLSheet = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#008000]'Disconnect from all Excel objects (let the user take over)
[/COLOR][/SIZE][SIZE=2]oXLBook = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2]oXLApp = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE]

Could anybody give me a hand? I really got stuck here for a long time... And I don't know what should I do when there is no error indicated in the code. Did I foget anything or make any stupid mistake again? Thanks a lot!

 

[CygNuS]

That's weird? no errors and visible is set to true yet you don't see it.

Go to task manager and close all excel.exe's, then put everything from line
oXlBook = oXlApp.Workbooks.Open... (this one also in comment)
to
Y2 = oXLSheet.Cells.Item...
in comment

and try again, you should at least now see excel open up. If not... there should be an excel.exe now in your task manager

Did you reference Microsoft 11.0 Object Library? What Office version do you have and what VB.NET? 2003 or 2005 (don't know if that matters)

 

[candice]

Thanks CygNus,
Now I use a button event and it works! However I don't know what's wrong with the form load event on my computer.
Anyway the problem is solved. thanks for your help again!

 

[JohnH]

However I don't know what's wrong with the form load event on my computer.

There's nothing wrong with the Form Load event, but you're not handling that event with your code, the sub method signature you use is wrong and there is no Handles statement. A typical event handler method for the Load event would look similar to the below code:

Private Sub frmDiv_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
     'here you can start writing code..
End Sub

You can have such event handler generated for you in code, in Code View you select the control and event in the comboboxes at top of page. For the default control event just doubleclick it in Designer View. (try doubleclicking some empty space of the form)