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.