导入的.xls访问的.mdb：外部表不是预期的格式 [英] Importing .xls to Access .mdb: External Table is not in the expected format

问题描述

我有一个宏，进口为preadsheet如下：（这个S preadsheet是一个基于Web的应用程序中的出口，以及初始出口时所选择的格式为97-2003）

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

在导入到Access，我得到：

When importing to Access, I get:

运行时错误3274：外部表不是预期的格式

当我打开这个Excel文件，我得到一个对话框

When I open this Excel file, I get a dialog

你正在试图打开该文件是不是由文件扩展名指定一个不同的格式...

所以，文件名是.xls的，我的电脑告诉我它的97-2003格式，但是当我打开该文件，然后点击保存，则默认为将其保存为网页格式可以选择保存为的.xls 。是什么给了？

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?

更新：如果我打开该文件，然后另存为.xls格式（看似多余，但显然不是），它问我，如果我想覆盖现有文件，所以我做的。有一次，我通过这一点，VBA导入成功。我不能有每周业务员通过这个过程 - 任何方式avoidd吗？从基于Web的应用程序可能是最初的出口？

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?

推荐答案

DoCmd.TransferS preadsheet 拒绝导入您的的.xls 文件，因为它不是一个真正的的.xls 文件，它是一个已经被赋予了的.xls 文件扩展名。提供了一个假的文件扩展名是我见过的其他开发商用一招，它确实是一个糟糕的主意（对于原因，我们在这里看到的）。

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).

如果上游系统阻在做正确的事和固定的code，产生的真正的的.xls 文件中的守护者然后尝试重命名的.xls 文件热媒，并使用其导入

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

这篇关于导入的.xls访问的.mdb：外部表不是预期的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！