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

Marcand · Accepted Answer · 2009-07-14 16:20:54Z

3

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

answered Jul 14, 2009 at 16:20

Marcand

3142 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff S · Accepted Answer · 2009-07-07 17:58:02Z

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

answered Jul 7, 2009 at 17:58

Jeff S

7,49432 silver badges32 bronze badges

1 Comment

Anonymous Type Over a year ago

or the HDR=NO; which depends on whether there is one.

Joe Erickson · Accepted Answer · 2009-07-07 17:32:08Z

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

answered Jul 7, 2009 at 17:32

Joe Erickson

7,2672 gold badges33 silver badges31 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:01:22Z

1

As well as using IMEX=1 in the connection string, you need to review a couple of registry keys. For more details, see this answer on SO.

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jul 13, 2009 at 23:15

onedaywhen

57.4k12 gold badges103 silver badges142 bronze badges

2 Comments

sigil Over a year ago

Link broken. Is there another source for the registry key solution?

onedaywhen Over a year ago

@sigil: I've changed the link to another SO answer, which has the same content as the aforementioned blog post (minus the comments).

Collectives™ on Stack Overflow

Null values reading data from Excel using ADO

4 Answers 4

Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related