到 .xlsx 文件的 VBA ADO 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18144838/
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
VBA ADO connection to .xlsx file
提问by GreenyMcDuff
I am trying to copy data from a closed Excel 2007 workbook (.xlsx) using an ADO connection.
我正在尝试使用 ADO 连接从关闭的 Excel 2007 工作簿 (.xlsx) 中复制数据。
I have the connection string working. But I get an automation error when I try to open the Command in the Recordset (Second to last line).
我有连接字符串工作。但是当我尝试打开 Recordset 中的命令（倒数第二行）时出现自动化错误。
This may not be clear in the below code so:
这在下面的代码中可能不清楚，所以：
"wsSummary" is a worksheet object "strSourceFile" is a string with the target data I need to copy from (e.g. Template.xlsx)
"wsSummary" 是一个工作表对象 "strSourceFile" 是一个字符串，其中包含我需要从中复制的目标数据（例如 Template.xlsx）
strSourceFile = wsSummary.Cells(nFirstRow + 4, 7)
strSheetSource = "Sheet1"
strSQL = "SELECT * FROM [" & strSheetSource & "]"
Set dbConnection = New ADODB.Connection
With dbConnection
.Provider = "Microsoft.ACE.OLEDB.12.0;"
.connectionString = "Data Source=" & strPOINTDataPath & strSourceFile & _
";Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";"
.ConnectionTimeout = 40
.Open
End With
If dbConnection = "" Then GoTo ErrorText
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = dbConnection
.CommandText = strSQL
End With
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = dbConnection
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open cmd
End With
采纳答案by Kazimierz Jawor
I think you missed $ characterin your SQL statement. Try to change appropriate line into this one:
我想你错过$ character了你的 SQL 语句。尝试将适当的行更改为这一行：
strSQL = "SELECT * FROM [" & strSheetSource & "$]"
or change strSheetSource variableinto this:
或者strSheetSource variable改成这样：
strSheetSource = "Sheet1$"
