Different version of Excel

Bob Hansen · Post by **Bob Hansen** » Mon Oct 08, 2012 8:59 pm

I am trying to work on a script that was probably written for a newer version of Excel. I am using Excel 2003 (11.0)

The following line is the original code, that returns an error:

Code: Select all

Let>XLINPUT=C:\Data\Temp\MyInfo.xlsx
Let>connStr=Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%XLINPUT%;Extended Properties="Excel 12.0 Xml;HDR=No";
DBConnect>connStr,dbH

I have tried both of these lines, but all three give me an error message that the provider cannot be found.

Code: Select all

Let>connStr=Provider=Microsoft.ACE.OLEDB.11.0;Data Source=%XLINPUT%;Extended Properties="Excel 11.0 Xml;HDR=No";

and

Code: Select all

Let>connStr=Provider=Microsoft.JET.OLEDB.11.0;Data Source=%XLINPUT%;Extended Properties="Excel 11.0 Xml;HDR=No";

%XLINPUT% is a valid spreadsheet. I have tried putting the file on a local drive and on a shared drive, makes no difference.
===========================================
Any suggestions please?

[/code]

Bob Hansen · Post by **Bob Hansen** » Mon Oct 08, 2012 10:01 pm

I have made some progress. Now I get another message vs. Provider Not Found. I had to add OLEDB: to the front and change from ACE to JET

Code: Select all

Let>XLINPUT=C:\Data\Temp\MyInfo.xlsx
Let>connSTR=OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Data Source=%XLINPUT%;Extended Properties="Excel 8.0;HDR=No";
DBConnect>connStr,dbH

This now gives me a new error message:
"ERR [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

I opened the spreadsheet, and saved it in 2003 format, made no difference.
I changed the string to use 11.0 vs. 8.0, made no difference.

What is wrong with this connection?

Me_again · Post by **Me_again** » Mon Oct 08, 2012 10:26 pm

What OS are you using?

Bob Hansen · Post by **Bob Hansen** » Mon Oct 08, 2012 11:57 pm

Using Windows7, SP1, 32 bit, with Macro Scheduler 12. Upgrading to version 13 now....

Looked at my ODBC Connection drivers and it looks like they may only be for 97-2000. Will be looking for a newer Excel ODBC driver.

Me_again · Post by **Me_again** » Tue Oct 09, 2012 12:18 am

I do recall reading something about ODBC and W7, like there is no driver. I'll try to find the reference.