Read excel file without opening it in vb net

Question

I've used earlier versions of VB (big access database) in the past, but VB.net 2022 is new to me. I've taken this excerpt from Excel and am trying to read it in one sheet. I've added the Microsoft Excel12.0 Object Library.

This code is in a button click event

strFileName = "F:\DarPlr\DlrData.xlsx"

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Open("strFileName") <<<<<<<exception thrown here
    xlWorkSheet = xlWorkBook.Worksheets("sheet1")
    'display the cells value B2
    MsgBox(xlWorkSheet.Cells(2, 2).value)
    'edit the cell with new value
    'xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
    xlWorkBook.Close()
    xlApp.Quit()

I get this exception: System.NullReferenceException: Object reference not set to an instance of an object. enter image description here

I tried using every example I could find, but they all contained mistakes of one kind or another. I attempted fixing the faults, but I was unsuccessful. I'm missing something, but I'm not sure what.

Answer 1

Instead of utilising the variable strFileName, you are attempting to access the XLS file using the string "strFileName".

strFileName = "F:\DarPlr\DlrData.xlsx"

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Open(strFileName) '<<<<<< use variable strFileName instead of the string "strFileName"
xlWorkSheet = xlWorkBook.Worksheets("sheet1")
'display the cells value B2
MsgBox(xlWorkSheet.Cells(2, 2).value)
'edit the cell with new value
'xlWorkSheet.Cells(2, 2) = "http://vb.net-informations.com"
xlWorkBook.Close()
xlApp.Quit()