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