[RESOLVED] Opening Excel with VB .NET

[thebuffalo]

Okay I am trying to make a class library that takes information from an excel file and puts it into an access database, but I keep getting this dumb object reference error that always screws me over. Here is my code:

Dim xlApp As Excel.Application
    Dim xlWbs As Excel.Workbooks
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlRange As Excel.Range
    Dim strExcelPath As New String("C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls")
    Dim accConn As String = "C:\Users\Aj\Desktop\TaxCert_be.accdb"
    Dim xlDT As New DataTable
    Dim xlDR As DataRow = xlDT.NewRow
    Public Sub XLMain()
        xlBook = xlWbs.Open(strExcelPath)
        xlSheet = xlBook.Worksheets(1)
        ReadXL()
        ImportXL()
        CloseExcel() 
    End Sub

Object reference not set to an instance of an object.
xlBook = xlWbs.Open(strExcelPath)

[Shaggy Hiker]

You never created xlWbs, so it is Nothing at the time of that call. You might be interested in looking up the Excel class I have over in the CodeBank, as it shows an example of opening these things in a Late Binding fashion, which is advantageous when it comes to Excel. On the other hand, the rest of the class doesn't do anything like what you are doing.

[thebuffalo]

Dim xlApp As Excel.Application
    Dim xlWbs As Excel.Workbooks
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlRange As Excel.Range
    Dim strExcelPath As New String("C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls")
    Dim accConn As String = "C:\Users\Aj\Desktop\TaxCert_be.accdb"
    Dim xlDT As New DataTable
    Dim xlDR As DataRow = xlDT.NewRow
    Public Sub XLMain()
        xlWbs = CreateObject("Excel.Application") ' Incorrect, error got thrown here.
        xlBook = xlWbs.Open(strExcelPath)
        xlSheet = xlBook.Worksheets(1)
        ReadXL()
        ImportXL()
        CloseExcel() 
    End Sub

Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel.Workbooks'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208DB-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

I have a feeling what I did wasn't right? I have Dim xlWbs As Excel.Workbooks, and the MSDN suggestion is to put a "new" in there, but it gives me an error saying I can't put that on an "interface"

[Shaggy Hiker]

You create xlApp with CreateObject, not xlWbs. After all, the former is the application, the latter is just a workbook within the application. Actually, I think you don't need xlWbs at all. I think you only need to create the app and open a workbook.

[thebuffalo]

Dim xlApp As Excel.Application
    Dim xlWbs As Excel.Workbooks
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim xlRange As Excel.Range
    Dim strExcelPath As String = "C:\Users\Aj\Desktop\Tax_Research_Template_2010_-_Excel.xls"
    Dim accConn As String = "C:\Users\Aj\Desktop\TaxCert_be.accdb"
    Dim xlDT As New DataTable
    Dim xlDR As DataRow = xlDT.NewRow
    Public Sub XLMain()
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Open(strExcelPath)
        xlSheet = xlBook.Worksheets(1)
        'Now Excel is open, do stuff
        ReadXL()
        ImportXL()
        'Close Excel:
        CloseExcel()  'Call Close Excel Sub
    End Sub

Public member 'Open' on type 'ApplicationClass' not found.
xlBook = xlApp.Open(strExcelPath)

How do I get xlBook as the "book" and open it so that I can set xlSheet as the sheet, I have googled a lot but all of the examples that I find and try, end up not working and giving me object reference errors. I even used an example from a codeguru article that failed me. Basically, I only need the xls file open to do this:

Try
            ordernumber = xlSheet.Cells(5, "C")
            owner1 = xlSheet.Cells(6, "C")
            county = xlSheet.Cells(6, "I")
            fulladdress = xlSheet.Cells(7, "C")
            address = Mid(fulladdress, 0, InStr(fulladdress, ",") - 1)
            zip = Mid(fulladdress, fulladdress.Length - 5, 5)
            city = Mid(fulladdress, address.Length + 2, InStr(fulladdress, ",") - 1)
            state = Mid(fulladdress, address.Length + city.Length + 4, 2)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

[Shaggy Hiker]

That much can all be found in the Excel class I have over in the CodeBank. Everything is type Object, but the names should pretty well explain what the variable holds.

[thebuffalo]

Protected Function OpenApp() As Boolean
        'Create the app.
        Try
            If xlApp Is Nothing Then
                xlApp = CreateObject("Excel.Application")
            End If
            xlApp.AlertBeforeOverwriting = False
            xlApp.DisplayAlerts = False
            ImOpen = True
            GetVersion()
        Catch ex As Exception
            If mVerbose Then
                Windows.Forms.MessageBox.Show(ex.Message, "Failed to Open Excel", MessageBoxButtons.OK, MessageBoxIcon.Information)
            End If
            ImOpen = False
        End Try
 
        OpenApp = ImOpen
    End Function

xlBook = xlApp.Workbooks.Open(mBookName, ReadOnly:=False, Notify:=False)

These are the only 2 things I see in there that have anything to do with opening the excel file to read from it, if it was somewhere else I must have missed it. Where do you set the location of the excel file?

xlBook = xlApp.Workbooks.Open(strExcelPath)
        xlSheet = xlBook.Worksheets(1)

I don't get an error with this until I get to

ordernumber = xlSheet.Cells(5, "C").ToString

and from there I get an error saying

Conversion from type 'Range' to type 'String' is not valid.

I turn options Strict On and I am forced to change everything so that it looks like this

xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = xlApp.Workbooks.Open(strExcelPath)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

and I try this

ordernumber = CStr(xlSheet.Cells(5, "C"))

And I still get this

Conversion from type 'Range' to type 'String' is not valid.

Is excel even opening? I have about 8 EXCEL.EXE *32 running in my process list but they don't seem to be taking any CPU or RAM.

[thebuffalo]

Public Sub XLMain()
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = xlApp.Workbooks.Open(strExcelPath)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
        'Now Excel is open, do stuff
        ReadXL()
        ImportXL()
        'Close Excel:
        CloseExcel()  'Call Close Excel Sub
    End Sub

ordernumber = xlSheet.Cells(5, "C").Value.ToString
            owner1 = xlSheet.Cells(6, "C").Value.ToString
            county = xlSheet.Cells(6, "I").Value.ToString
            fulladdress = xlSheet.Cells(7, "C").Value.ToString

Works. Thanks.