Read excel file without opening it in vb net

0 votes

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. Net-informations.com http://vb.net-informations.com/excel-2007/vb.net_excel_2007_open_file.htm 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.

Apr 9, 2023 in Others by Kithuzzz
• 38,010 points
1,405 views

1 answer to this question.

0 votes

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()
answered Apr 9, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

I want to make Excel read a value in Calc and copy it to my sheet in Excel

Here is the sample code that will allow ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,420 points
290 views
0 votes
0 answers

PHP Read Online Excel File Without Download The File

I am creating web stock apps. I ...READ MORE

Nov 2, 2022 in Others by Kithuzzz
• 38,010 points
537 views
0 votes
1 answer

How to add Header and Footer in excel with vb.net?

This code will work: Imports Microsoft.Office.Interop Private Sub Button1_Click(sender ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,420 points
513 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
908 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,090 points
831 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,090 points
4,095 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,846 views
0 votes
1 answer
0 votes
1 answer

Download file from URL in Excel 2019 (it works on Excel 2007)

The Sub Code looks fine. Check the ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
1,394 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP