ADO from .xls to .xlsm file - "Unrecognized database format" error

[Tara H]

I'm trying to use ADO to pull data from a .xls file into a .xlsm file in
Excel 2007. Whatever I do gives me the error:

Run-time error '-2147467259 (80004005)':
Unrecognized database format

I've searched for this and found some results relating to corrupted files,
but I doubt this can be the case - I've tried it with a number of different
input files and even created one by hand from scratch to test with.

I've tried many different samples of code that I've found on this forum, and
each of them gives the same error at the 'cn.open' point. The connection
string I'm currently using is:

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data
Source=C:\temp\testbook.xls;"

I'm not entirely sure if I'm using the right driver and references, since I
haven't been able to find an example of my exact situation.

Any guidance would be very gratefully received - I've been tearing my hair
out over this and making no progress!

 

[Bob Phillips]

This works okay for me

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

 

[Tara H]

Thanks Bob, I'm still not really sure where I was going wrong - it may have
been in the format of the connection string, but when I copied yours it
worked ok and got me past this blockage.
Thanks for the help!

 

[Bob Phillips]

You were trying to use the Microsoft.ACE.OLEDB.12.0 OLE DB provider, I must
admit I haven't come across that one before.

 

[Tara H]

It seems to be the one to use for opening the 2007 files - after getting your
example to work I tweaked it for a while and it's now working correctly with:

"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=Excel 12.0 Xml;"

for .xlsx files and

"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"

for .xls files.

The sample I had at the beginning seemed to imply that which one to use
depended on which version of Excel you were pulling the data _into_, but my
experimenting revealed that it was the _source_ that was important. This may
be obvious to people familiar with ADO, but maybe this will be useful to
someone like me who's just starting with it

Thanks again for getting me on the right track.

_________________