Excel Microsoft Query: External table is not in the expected format

JohnCM

New Member
Joined
Sep 4, 2008
Messages
43
All,

I am receiving an error from the Microsoft Query wizard when I try and create a query of an xlsx or xlsm file. The error is "External table is not in the expected format."

Can anyone tell me why this error is coming up and how I can correct the file so that I can write a query to pull info from a Named Range in the file.

I have written some code to duplicate the Microsoft query using ADODB but get the same error message when the Open connection line runs.

I have been using this method with these files for a while now but curiously it has quit working as of late. This is very frustrating as this is a critical method of my workflow.


Thanks for any help you can provide.

John M.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Same error here. Seems as though Excel 2007 cannot query .xlsx files, only .xls. At least that's the case for me. I'm trying to find a workaround. I know it's fixed in Excel 2010 but at work we're just getting onto 2007. Nice work MS!
 
Upvote 0
Although xenou's response is 100% correct, I just wanted to point out something that I noticed, a quirk if you will:
If you are running the ADO query from the same workbook that you're connecting to, you can still use the JET connection with .xlsx files. Why you would want to do this is either
  1. ignorance (as in my case until I found this helpful post) or
  2. the small advantage in speed offered by the JET connection.
ADO makes parsing large files extraordinarily fast. The code that this replaced took about a minute to run in entirety, while ADO allowed me to do it in a quarter of the time.
Here is a comparison of the macro times while doing the exact same procedures on the recordset.


RESULTS:​

ADO using JETADO using ACE
10.31250.3671875
20.32031250.375
30.31250.359375
40.31250.375
50.31250.3671875
Tot.1.5703125 sec1.84375 sec

<tbody>
</tbody>

CODE:
(Run under the exact same conditions—the source workbook was open for both)​
JET Connection:
Code:
Sub CompareJET()
    Dim objConnection As Object
    Dim objRecordset As Object
    Dim dblStart1 As Double
    Dim dblEnd1 As Double
'   ...<defined several="" other="" variables="">
    
    dblStart1 = Timer
    Const adOpenstatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
[B]    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _[/B]
[B]        "Data Source=S:\Manufacturing\PDM\PDM.xlsm;" & _[/B]
[B]        "Extended Properties=""Excel 8.0;HDR=Yes;"";"[/B]

    objRecordset.Open "Select * FROM [SinPrints$] Where [ID] = 1", _
        objConnection, adOpenstatic, adLockOptimistic, adCmdText

'<identical code="" run="" on="" ~100="" records="" and="" 10="" fields
Identical code run on 100 records and 10 fields

    objRecordset.Close
    objConnection.Close
    dblEnd1 = Timer - dblStart1
    Range("B1").End(xlDown).Offset(1, 0).Value2 = dblEnd1
End Sub

ACE Connection:
Code:
Sub CompareJET()
    Dim objConnection As Object
    Dim objRecordset As Object
    Dim dblStart2 As Double
    Dim dblEnd2 As Double
'   ...<defined several="" other="" variables="">
    
    dblStart2 = Timer
    Const adOpenstatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
[B]    objConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _[/B]
[B]        "Data Source=S:\\Manufacturing\PDM\PDM.xlsm;" & _[/B]
[B]        "Extended Properties=""Excel 12.0;HDR=Yes;"";"[/B]

    objRecordset.Open "Select * FROM [SinPrints$] Where [ID] = 1", _
        objConnection, adOpenstatic, adLockOptimistic, adCmdText

'<identical code="" run="" on="" ~100="" records="" and="" 10="" fields
Identical code run on 100 records and 10 fields

    objRecordset.Close
    objConnection.Close
    dblEnd2 = Timer - dblStart2
    Range("C1").End(xlDown).Offset(1, 0).Value2 = dblEnd2
End Sub

I know that this information may be insubstantial, but since I've profited from this forum so much in the past, I thought that I'd contribute something that I've learned. Thanks!</identical></defined></identical></defined>
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top