Importing .xlsm, .xlsx with OLEDB

[Jeff]

I have been trying to write a macro to open a closed workbook, copy a
worksheet from it, and then use that data as I need. After surfing the
internet a bit, I was able to find out how to do this using ADO and it was
very useful when operating on .xls workbooks. Now I am trying to use it on
..xlsx and .xlsm workbooks and it fails each time with an error saying
"External table is not in the expected format."

I am using Excel 2007 SP1 and have attempted to install the Access Database
Engine off of the Microsoft website to enable OLEDB to read the new formats.

This is the code that I am using to try to connect:
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
ElseIf SourceFile Like "*.xlsm" Then
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0 Macro;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"

Are there other files that I need to download? Is there something I am
missing in my syntax? Can anyone help me?


Thank you,
Jeff

 

[Ron de Bruin]

Test my code on this page first on a xlsm file
http://www.rondebruin.nl/ado.htm

 

[Jeff]

Funny that you should reply, because your code is actually what I based my
own macro off of. Thank you for that.

Your code does work when i save the test.xls file as test.xlsm. I used your
example 4 because it is most similar to what I am doing. When I use the same
ADO tester on another workbook it fails. I tried to add "Sheet1" to one of
my test workbooks, and also tried changing the sheet that your code pulls
data from, but neither worked. I did find that if a workbook is already open
then my macro works fine, but if it is closed I receive the same error as
before.

Could there actually be a formatting difference between two .xlsm files?

Jeff

 

[Ron de Bruin]

Hi Jeff

If you want send me the file private and I take a look at it this week

 

[Jeff]

So, I never exactly found out what was wrong with the files, but I decided to
create a new workbook, saved it as a .xlsm file, and copied each sheet and
macro over to it from the files that I needed to import (fortunately there
aren't that many at the moment). Once I did that, I had no problems
importing the files. I guess it doesn't much matter now, but do you know any
reason that could happen?

Jeff

 

[Ron de Bruin]

Hi Jeff

reason that could happen?

No, sorry