ADO connection to Excel 2007 *.xlsx files?

[Robert Crandal]

I am able to make an ADO connection to files of type "*.xls"
using the following connection string:

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyData\data2.xls;Extended Properties=""Excel
8.0;HDR=NO;IMEX=1"""

However, the above connection string does NOT work if my Excel
file is of type "*.xlsx". In fact, if I try to connect to an "xlsx" file
using the above
string, I get an error message that says "External table is not in the
expected format"!

What am I doing wrong? Do I need to modify the connection string to be
able
to read *.xlsx fileS?

Thank you!

 

[Harald Staff]

Hi Robert

Bookmark this site:
http://www.connectionstrings.com/

HTH. Best wishes Harald

 

[GS]

Robert Crandal brought next idea :

I am able to make an ADO connection to files of type "*.xls"
using the following connection string:

szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyData\data2.xls;Extended Properties=""Excel
8.0;HDR=NO;IMEX=1"""

However, the above connection string does NOT work if my Excel
file is of type "*.xlsx". In fact, if I try to connect to an "xlsx" file
using the above
string, I get an error message that says "External table is not in the
expected format"!

What am I doing wrong? Do I need to modify the connection string to be able
to read *.xlsx fileS?

Thank you!

It's with all the double quotes!!!

Try this:
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"DataSource=C;\MyData\data2.xls;" & -
"ExtendedProperties=""Excel 8.0;HDR=No;IMEX=1"";"

Note that while multiple ExtendedProperties are wrapped in double
quotes AND delimited by the ; character, the string must be ended with
another ; character followed by the normal closing double quote.

IOW, your string is missing the ; character before the closing double
quote!

 

[GS]

I forgot to mention that the answer to your issue with Excel 2007 files
lies with the link Harald provided. Look below the initial list and
click on the link to Excel 2007. It explains fully the same construct
as you're using for XLS files, but using
Provider=Microsoft.ACE.OLEDB.12.0, and ExtendedProperties=Excel 12.0.
This, then, precludes that if the files you're working with are in both
formats you'll have to determine this before passing the connection
string. IOW, you need 2 connection strings for provider AND 2 for
ExtendedProperties. So you could set something up like this:

Const sProvider As String = "Microsoft.Jet.4.0;"
Const sProvider12 As String = "Microsoft.Ace.12.0;"
Const sExtProps As String = "Excel 8.0;"
Const sExtProps12 As String = "Excel 12.0;"

Simply test the file extension of each file beforehand and construct
the connection string accordingly.

P.S.
I noticed that in your sample szConnect you used a ; character after
the drive letter instead of : in the DataSource= line!

 

[GS]

Oops! Change sExtProps const to "Excel 12.0 Xml;"

Sorry about that!<g>

 

[GS]

GS was thinking very hard :

Oops! Change sExtProps const to "Excel 12.0 Xml;"

Sorry about that!<g>

Geez.., that should have read "Change sExtProps12 to..."

 

[Robert Crandal]

Thanks! I finally got this one to work. I never realized this would
be such a pain to setup, haha! 8)

 

[GS]

Robert Crandal presented the following explanation :

Thanks! I finally got this one to work. I never realized this would
be such a pain to setup, haha! 8)

Well, the beauty of it is once it's done it's all reusable for other
projects!<g>

 

[GS]

Robert Crandal has brought this to us :

Thanks! I finally got this one to work. I never realized this would
be such a pain to setup, haha! 8)

I should have clarified that my last postings regarding the change to
sExtProps12 was only to conform with what you see in Harald's link. My
original posting works 'as is' and is what I use because that's what my
mentor (Rob Bovey) recommends in his book 'Excel 2007 VBA' as the
update to the sample you downloaded from his website earlier.