excel - Importing .xls to Access .mdb: External Table is not in the expected format

Question

I have a macro that imports a spreadsheet as follows: (this spreadsheet is an export from a web-based application, and during the initial export the chosen format is 97-2003)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "d2s_safety_tbl", _
"\company.comdfsroot$Shareoffice_publicD2SD2SD2S_ScorecardSource DataD2SD2S Safety.xls", True

When importing to Access, I get:

Run-time Error '3274': External table is not in the expected format.

When I open this Excel file, I get a dialog

"The file you are trying to open is in a different format than specified by the file extension..."

So the file name is .xls, my computer tells me its the 97-2003 Format, but once I open the file and click save, it defaults to save it as a Web Page format with the option to save as .xls. What gives?

UPDATE: If I open the file, then Save As .xls format (seemingly redundant, but apparently not), it asks me if I want to overwrite the existing file, so I do. Once I go through this, the VBA import is successful. I can't have the clerk go through this process every week--any way to avoidd this? Possibly the initial export from the web-based application?

Answer 1

DoCmd.TransferSpreadsheet is refusing to import your .xls file because it is not really an .xls file, it is an HTML file that has been given an .xls file extension. Providing a "fake" file extension is a trick that I've seen other "developers" use, and it really is a Bad Idea (for the reasons we've seen here).

If the keepers of the upstream system balk at doing The Right Thing and fixing their code to produce a real .xls file then try renaming the ".xls" file to .htm and importing it using

DoCmd.TransferText acImportHTML, ...