XL 源代码和文本输出的 Excel 2010 VBA/ADO 连接错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14734489/
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
Excel 2010 VBA/ADO connection error with XL source and text output
提问by Jim Snyder
I am using Excel 2010 with a macro to access another daily spreadsheet to pull data for forming an FTP file of records. The specific problem I am having is a runtime connection error. The error I am getting is '-2147467259(80004005)': Unrecognized database format 'C:\Work\Daily FTP Process\Excel DBs and Files\ftp.xlsx'. All I need it to know where to look. Here is the connection string from the watch:
我正在使用带有宏的 Excel 2010 来访问另一个每日电子表格以提取数据以形成记录的 FTP 文件。我遇到的具体问题是运行时连接错误。我得到的错误是“-2147467259(80004005)”：无法识别的数据库格式“C:\Work\Daily FTP Process\Excel DBs and Files\ftp.xlsx”。我只需要它就知道在哪里看。这是手表的连接字符串：
: ConnectionString : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Daily FTP >Process\Excel DBs and Files\ftp.xlsx;" : String : Module1.XLFixedFieldFile
: ConnectionString : "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Work\Daily FTP >Process\Excel DBs and Files\ftp.xlsx;" ：字符串：Module1.XLFixedFieldFile
Here is the pertinent (or impertinent) code:
这是相关（或不恰当）的代码：
Dim conn As Object
Dim cmd As Object
Dim psidRecSet As Object
Dim loopIndex As Long
Dim connString As String
Dim sqlString As String
Set conn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
connString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & XLName & ";"
conn.Open connString <==== Here is the line where it is breaking
回答by Jim Snyder
SOLVED! The connectionstring that worked is as follows:
解决了！工作的连接字符串如下：
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & XLName &
"';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"
Remou, I had tried that, but had something wrong and got an error. The copy&paste of a working line found in another forum was my salvation. I appreciate the help.
Remou，我已经尝试过了，但是出了点问题并且出错了。在另一个论坛上找到的工作行的复制和粘贴是我的救赎。我很感激你的帮助。
For others using this yto solve their problems, to work with Excel 2010, I went to "Tools/Reference" and enabled Microsoft AcriveX Data Objects 6.1 Library and Microsoft ActiveX Data Objects Recordset 6.0 Library.
对于使用此工具解决问题的其他人，为了使用 Excel 2010，我转到“工具/参考”并启用 Microsoft AcriveX Data Objects 6.1 Library 和 Microsoft ActiveX Data Objects Recordset 6.0 Library。
Dim conn As Object
Set conn = CreateObject("ADODB.Connection")
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & XLName &
"';Extended Properties='Excel 12.0;HDR=NO;IMEX=1';"
conn.Open connString
回答by Fionnuala
You want:
你要：
connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
& xlFile & ";Extended Properties='Excel 12.0 Xml;HDR=No;IMEX=1';"
Note the quotes for the extended property.
请注意扩展属性的引号。
You might like to read http://support.microsoft.com/kb/257819, with particular reference to IMEX.
您可能喜欢阅读http://support.microsoft.com/kb/257819，特别提到 IMEX。
