ADO connection to Excel 2007 *.xlsx files?
 

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!

ADO connection to Excel 2007 *.xlsx files?
 

Hi Robert

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

HTH. Best wishes Harald


"Robert Crandal" wrote in message
...
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!

ADO connection to Excel 2007 *.xlsx files?
 

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!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ADO connection to Excel 2007 *.xlsx files?
 

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[s] 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!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ADO connection to Excel 2007 *.xlsx files?
 

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

Sorry about that!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ADO connection to Excel 2007 *.xlsx files?
 

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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ADO connection to Excel 2007 *.xlsx files?
 

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



"GS" wrote in message
...
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..."

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ADO connection to Excel 2007 *.xlsx files?
 

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)



"GS" wrote in message
...
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..."

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



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

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

ADO connection to Excel 2007 *.xlsx files?
 

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)



"GS" wrote in message
...
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..."

-- Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc



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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc