Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I convert .xlsx to .xls Excel files? | Excel Discussion (Misc queries) | |||
How can I batch convert 97-2003 .xls files to 2007 .xlsx files | Excel Discussion (Misc queries) | |||
Excel 2007 on Windows XP computer will not open .xlsx files. | Excel Discussion (Misc queries) | |||
Opening .xlsx files in separate instances of Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 xlsx files open as Read-Only in Excel 2003 after Compat | Excel Discussion (Misc queries) |