Importing .xls to Access .mdb: External Table is n

问题:

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.com\dfsroot$\Share\office_public\D2S\D2S\D2S_Scorecard\Source Data\D2S\D2S 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?

回答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, ...

回答2:

I also got a 3274 error when importing a spreadsheet into Access. I have been using this macro for a while now.

The solution was to compact and repair the Access database.

回答3:

I had the same problem along with another problem (office 2016 x64): Pasting from excel to access raised 'Data on the Clipboard is damaged,...' error. I found a workaround by clicking on the lower right button in paste section of home tab of access, opening the clipboard pane. After opening Clipborad Pane as stated above, my DoCmd.TransferSpreadsheet surprisingly worked fine. I don't know why but it may help those having the same problem and those trying to find a real solution.