vba 运行时错误：外部表不是预期的格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25356832/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run Time Error: External table is not in the expected format
提问by L42
I am trying to pull data from a Shared Drivewithout opening the file.
I used ADOso I can manipulate the resulting table easily since I just need specific fields.
Rather than doing the filter and copy routine, ADOis preferable since I can use sql queryto get what I want.
我试图在不打开文件的情况下从共享驱动器中提取数据。
我使用了ADO，因此我可以轻松地操作结果表，因为我只需要特定的字段。
与执行过滤器和复制例程相比，ADO更可取，因为我可以使用sql 查询来获取我想要的内容。
The line: rec.Open sqlStr, con, adOpenStatic, adLockReadOnly
线路： rec.Open sqlStr, con, adOpenStatic, adLockReadOnly
throws the error
抛出错误
Run-time error ...:
External table is not in the expected format.
运行时错误...：
外部表不是预期的格式。
The file is in .xlsformat and it is included in the Trust Centerdefault setting of blocked files (files that will be opened in Protected View).
该文件为.xls格式，它包含在受阻止文件（将在受保护视图中打开的文件）的信任中心默认设置中。
Out of curiosity, I temporarily disabled the file blocking but the same error occurred.
出于好奇，我暂时禁用了文件阻止，但发生了同样的错误。
When I open the file and run the code, it retrieves the data.
当我打开文件并运行代码时，它会检索数据。
How can I make this work without opening the file?
如何在不打开文件的情况下完成这项工作？
Code I use:
我使用的代码：
Sub stancial()
Dim sPath As String, fName As String
sPath = "P:\Folder\": fName = "Report.xls"
Dim con As ADODB.Connection: Set con = New ADODB.Connection
Dim rec As ADODB.Recordset: Set rec = New ADODB.Recordset
Dim DataSource As String
DataSource = sPath & fName
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & DataSource & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Dim cat As ADOX.Catalog: Set cat = New ADOX.Catalog
Set cat.ActiveConnection = con
Dim shName As String
shName = Replace(cat.Tables(0).Name, "'", "")
Dim sqlStr As String
sqlStr = "SELECT * FROM [" & shName & "];"
rec.Open sqlStr, con, adOpenStatic, adLockReadOnly
Sheet1.Range("A2").CopyFromRecordset rec
rec.Close
con.Close
Set cat = Nothing
Set rec = Nothing
Set con = Nothing
End Sub
Edit1:
编辑1：
I also tried this:
我也试过这个：
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & DataSource & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
and also this:
还有这个：
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & DataSource & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"
It still gives the error. I think is has something to do with opening the Recordsetrather than the Connection.
它仍然给出错误。我认为这与打开Recordset而不是Connection 有关系。
采纳答案by Axel Richter
If this is a machine generated XLS-File, I suspect the machine, that is generating this, does something wrong. Try open the file in Excel, save it with different filename in Excel 97-2003 xls format. Put it on the shared drive and then try with this file. If it works then, my suspect was correct.
如果这是一台机器生成的 XLS 文件，我怀疑生成这个文件的机器做错了什么。尝试在 Excel 中打开文件，以 Excel 97-2003 xls 格式使用不同的文件名保存它。将它放在共享驱动器上，然后尝试使用此文件。如果它当时有效，我的怀疑是正确的。
Btw.: That it works with open files has not really something to say. If you have open a test.csv file in Excel, then you can access even this with ADO and the Excel-driver.
顺便说一句：它适用于打开的文件并没有什么可说的。如果您在 Excel 中打开了 test.csv 文件，那么您甚至可以使用 ADO 和 Excel 驱动程序访问该文件。
You can check this, if you try:
你可以检查这个，如果你尝试：
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & "C:\path\test.csv" & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
this will fail, if test.csv is not open in Excel. Now open test.csv in Excel and try again. Now it will succeed.
如果 test.csv 未在 Excel 中打开，这将失败。现在在 Excel 中打开 test.csv 并重试。现在它会成功。
That in your case it fails so late, suggests that the error is not large enough to prevent even the connection.
在您的情况下，它如此晚才失败，这表明错误不足以阻止连接。
Greetings
你好
Axel
阿克塞尔
回答by W-hit
I know this is an old post, but for anyone currently having this problem what seems like randomly, I've found it may still have to do with your connection string not including IMEX=1.
我知道这是一篇旧帖子，但对于目前遇到此问题的任何人来说，这似乎是随机的，我发现它可能仍然与您的连接字符串有关，不包括IMEX=1.
Example: con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
例子： con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filepath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider the scenario that one Excel file might work fine cause that file's data causes the driver to guess one data type while another file, containing other data, causes the driver to guess another data type. This can cause your app to crash.
始终使用 IMEX=1 是一种更安全的方式来检索混合数据列的数据。考虑这样一种情况：一个 Excel 文件可能工作正常，导致该文件的数据导致驱动程序猜测一种数据类型，而包含其他数据的另一个文件导致驱动程序猜测另一种数据类型。这可能会导致您的应用程序崩溃。
A helpful source for connection strings can be found at: https://www.connectionstrings.com/excel/
可以在以下位置找到连接字符串的有用来源：https: //www.connectionstrings.com/excel/
