Excel – Null values reading data from Excel using ADO

adoexcelexcel-2007vba

I am reading data from an Excel 2007 spreadsheet using ADO. Setting up the connection is easy:

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 any trouble on this object. However, when I try to query the data:

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

I simply get a table full of Nulls.

This is mentioned as an issue on the Microsoft Support site – but I have explicitly enabled "Import Mode" (as you can see in the code above – IMEX=1).

Best Answer

The Execute method does not return any records as it is for action queries. Your might want to try the OpenRecordset method.

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