Geek Answers Handbook

Zero values ​​reading data from Excel using ADO

I am reading data from an Excel 2007 spreadsheet using ADO. Connection setup is simple:

Dim ado As ADODB.Connection Set ado = CreateObject("ADODB.Connection") ado.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFilename.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";" ado.Open

I can call ado.OpenSchema without problems on this object. However, when I try to request data:

 Dim rs As ADODB.recordSet Set rs = ado.Execute("SELECT * FROM [Current Work Load$]")

I just get a table full of Nulls.

This is referred to as a problem on the Microsoft support site - but I explicitly turned on the import mode (as you can see in the code above - IMEX=1 ).

+4
source share
4 answers

The Execute method does not return any records, as for action requests. You might want to try the OpenRecordset method.

 Dim rs As ADODB.recordSet Set rs = ado.OpenRecordset("SELECT * FROM [Current Work Load$]")
+2
source

I found that the ADO connection strings here are incredibly legible. I read spreadsheets to work, but with a slightly different connection string:

Provider = Microsoft.ACE.OLEDB.12.0; Data Source = "+ file_name + @"; Advanced Properties = "Excel 12.0; IMEX = 1";

(I do not have XML after Excel 12.0 declaration).

+2
source

SpreadsheetGear for .NET can read Excel workbooks and allows you to access any cells without any problems / limits that you may encounter ADO.

You can see live C # and VB samples here and download a free trial.

Disclaimer: I have SpreadsheetGear LLC

+1
source

Also, using IMEX=1 in the connection string, you need to look at several registry keys. See this answer on SO for more information.

+1
source

More articles:


All Articles