Null values reading data from Excel using ADO

Question

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).

Answer 1

SpreadsheetGear for .NET can read Excel workbooks and enables you to access any cells without the kinds of issues / limatations you can run into with ADO.

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

Disclaimer: I own SpreadsheetGear LLC

Answer 2

I've found the ADO connection strings here are unbelievably picky. I've gotten reading the spreadsheets to work but with a slightly different connection string:

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

(I don't have the XML after the Excel 12.0 declaration).

Answer 3

As well as using IMEX=1 in the connection string, you need to review a couple of registry keys. For more details, see the excellent (coughs) Daily Dose of Excel blog:

External Data - Mixed Data Types

There is a lot of good stuff in the copious comments.

I really should get around to consolidating those comments into a new post. One day...

Answer 4

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$]")

Answer 5

Here is the proper working code http://chetanwarade.wordpress.com/2010/08/18/read-excel-with-ado-net/