External Table is not in expected format error

[KeythStone]

Hello to all,

I'm attempting to run an Access macro to output a file. It was working fine and then the last couple days I've received the error: 'External table is in unexpected format'

Access version: 2010
output file: Excel .xls (though i've tried with .xlsx)
macro action: importexportspreadsheet
transfer type :export
spreadsheet type Microsoft Excel 97-2003
tablename: static query which uses pass-through queries
with ranges

Troubleshooting:

I've tried different exporting actions Exportwithformatting, outputto, to no avail.
I've redesigned the destination file, both with and without ranges.
I've relinked the pass-through query ODBC to server for all relevant queries.
I've compact and repaired database.
Each pass-through query runs without errors.
The static query also performs without errors.
I've changed the macro action to 'importexporttext' and it works.
I do not use VBA code for this process.

Questions:
How should I go about troubleshooting?
Any suggestions?

Thanks for all your help and remember
Stay Smooth,
KeythStone

 

[KeythStone]

Here's an update to my issue:

I repaired both Access and Office Suite (both 2010)
Checking Event Viewer for Microsoft Office Alerts and found this:




- EventID 300


[ Qualifiers] 0



Level 4


0




- TimeCreated


[ SystemTime] 2012-05-09T12:33:50.000000000Z



EventRecordID 10338







- EventData


Microsoft Access


External table is not in the expected format.


503274


14.0.6029.1000

 

[KeythStone]

Update:

In order for me to complete the above action, that is, export to an excel file without receiving 'External table is not in expected format' msg is that I've used VB Docmd.Outputto which works.

It didn't work the first time as it stated a similar message, to the effect that the format was incorrect. I then changed the Docmd.outputto properties to prompt me to select a format. By doing so I was able to export to .xls file.

I then changed the properties back to acFormatXLS and re-ran the script. It worked. I'm still completely lost on what is happening and how a resolution was reached. I will continue to monitor the issue and comment if it persists.

Keep it Smooth,
KeythStone