Reading from Excel using VB problem

[MR Campbell]

At present I am attempting a rather simple task ... to read a cell from an existing sheet from an existing Excel file using Visual Basic (Visual Studio 2010). I am unable to do this as I am getting an error message at the indicated line. I have already added a reference to Excel 14.0 (Excel 2010) within my VB project. Any ideas ?

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim FirstName, Surname As String

'file path and name for the Excel file
Dim FileNamePath As String = "C:\Users\MRC\Desktop\MRC Files\Visual Basic Files\SwimManager 2014\CurrentSeasonDATA\SquadData.xls"

Dim XL = CreateObject("Excel.Application") 'reference Excel as the application
XL.Visible = False 'make Excel application invisible

XL.Workbooks.Open(FileNamePath) 'open the designated workbook file

FirstName = XL.Worksheets("DOB").Cells(6, 3).Value 'read the first name <======== getting the ERROR ON THIS LINE
Surname = XL.Worksheets("DOB").Cells(6, 4).Value 'read the surname

'Write the name to a textbox
TextBox2.Text = FirstName & " " & Surname
End Sub

 

[Sektor]

Dim XL = CreateObject("Excel.Application") 'reference Excel as the application
XL.Visible = False 'make Excel application invisible

XL.Workbooks.Open(FileNamePath) 'open the designated workbook file

FirstName = XL.Worksheets("DOB").Cells(6, 3).Value 'read the first name <======== getting the ERROR ON THIS LINE
Surname = XL.Worksheets("DOB").Cells(6, 4).Value 'read the surname

1. You open the workbook, but does not catch Workbook object which Open method returns. Thus "XL.Worksheets("DOB").Cells(6, 3).Value" does not make a sense.
2. Which options your project has? Option Strict? Option Explicit?

 

[MR Campbell]

Option Explicit On !
Does this setting matter ?

 

[Sektor]

Moreover, if you add reference to library, then why not using it? You, instead, you late-binding instead of early-binding. By using CreateObject you use late-binding. If you'd use early-binding then you could write like this:

Imports Excel = Microsoft.Interop....

Sub FFF()
....
    Dim xlApp As Excel.Application
    Dim book As Excel.Workbook
    Dim sheet As Excel.Worksheet
.....
     xlApp = New Excel.Application {.Visible = False}
     book = xlApp[COLOR=#333333].Workbooks.Open(FileNamePath)
[/COLOR]     sheet = DirectCast(xlBook.Worksheets("DOB"), Excel.Worksheet)
....
End Sub

Also, you enable IntelliSense, which will help you with pop-up list of methods and properties.

 

[MR Campbell]

ok ... here is what I now have :

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

xl = New Excel.Application {.Visible = False}
wb = xl.Workbooks.Open(FileNamePath)
sht = DirectCast(wb.Worksheets("DOB"), Excel.Worksheet)

FirstName = sht.Cells(6, 3).Value 'read the first name
Surname = sht.Cells(6, 4).Value 'read the surname

Now I get 4 build errors : Excel.Application is not defined, Excel.Workbook is not defined and Excel.Worksheet is not defined.

I didn't understand what you wrote when you said : Imports Excel = Microsoft.Interop....

 

[Sektor]

I apologize that I didn't include correct Imports. Here's correct one:

Imports Excel = Microsoft.Office.Interop.Excel

The "Excel" before "equals" sign is an alias for namespace, so that it'd be easier to refer to "Microsoft.Office.Interop.Excel".

 

[MR Campbell]

Ok ... I understand now the Imports Excel ......
Now it is working ... thankyou very much !

 

[Sektor]

You're welcome!

 

[MR Campbell]

The question I now have is how to close the file that has been read.
i.e. how to close the file ....in this case since I have simply read from it, I don't want to save the file.

Here is what I currently have :

Dim FirstName, Surname As String

'file path and name for the Excel file
Dim FileNamePath As String = "C:\Users\MRC\Desktop\MRC Files\Visual Basic Files\SwimManager 2014\CurrentSeasonDATA\SquadData.xls"

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

xl = New Excel.Application 'run a new Excel application
xl.Visible = False 'make Excel invisible

wb = xl.Workbooks.Open(FileNamePath) 'open the workbook file
sht = DirectCast(wb.Worksheets("DOB"), Excel.Worksheet) 'define the worksheet

FirstName = sht.Cells(6, 3).Value 'read the first name
Surname = sht.Cells(6, 4).Value 'read the surname

'Write the name to a textbox
TextBox2.Text = FirstName & " " & Surname

 

[Sektor]

Here's working code I have tested (console app). It opens workbook and closes it without saving changes.

Imports Excel = Microsoft.Office.Interop.Excel


Module Module1


    Sub Main()


        Dim xlApp As Excel.Application
        Dim book As Excel.Workbook
        Dim sheet As Excel.Worksheet
        Dim FileNamePath As String


        FileNamePath = "C:\SomeFile.xlsx"
        xlApp = New Excel.Application With {.Visible = True}
        book = xlApp.Workbooks.Open(FileNamePath)
        sheet = DirectCast(book.Worksheets("DOB"), Excel.Worksheet)


        book.Close(SaveChanges:=False)
        xlApp.Quit()


        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        GC.Collect()
        GC.WaitForFullGCComplete()


        Console.WriteLine("Done!")


    End Sub


End Module