在 Excel VBA 中使用 SQL 访问其他 Excel 工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42726971/
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
Use SQL in Excel VBA to access other Excel workbook
提问by djblois
I am trying to write a connection string and SQL script to run a query in Excel to pull data from another Excel workbook. This is what I have currently:
我正在尝试编写一个连接字符串和 SQL 脚本来在 Excel 中运行查询以从另一个 Excel 工作簿中提取数据。这是我目前所拥有的：
Sub Test()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim sql As String
' Create the connection string.
sConnString = "provider=Microsoft.Jet.OLEDB.4.0;data source=" & _
"C:\Users\dblois\Desktop\Shareenas Report.xlsx" + ";Extended Properties=Excel 8.0;"
' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
sql = "SELECT * FROM [Data$A1:AC73333]"
' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute(sql)
' Check we have data.
If Not rs.EOF Then
' Transfer result.
Sheets(1).Range("A1").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub
I keep getting the following when I try to open the connection. What is wrong with my connection string?
当我尝试打开连接时，我不断收到以下信息。我的连接字符串有什么问题？
External table is not in the expected format
外部表不是预期的格式
I found the Followingand changed my code to be:
我找到了以下并将我的代码更改为：
sConnString = "provider=Microsoft.Jet.OLEDB.12.0;data source=" & _
"C:\Users\dblois\Desktop\Shareenas Report.xlsx" + ";Extended Properties=Excel 12.0;"
Then I am getting the following error:
然后我收到以下错误：
Provider cannot be found. It may not be property installed
找不到提供者。它可能没有安装属性
回答by Parfait
In first attempt your OLEDB driver is not appropriate for Excel file type. In second attempt, you have an incorrect OLEDB Driver as there is no 12.0 version for Jet. As @Comintern comments and answers in the very link you posted, use the ACE driver version. But do note for both types, drivers' 32/64-bit versions must match your MS Office bit version or any other program even languages (i.e., PHP, Python, Java) that you attempt to connect to Excel data source.
第一次尝试时，您的 OLEDB 驱动程序不适合 Excel 文件类型。在第二次尝试中，您的 OLEDB 驱动程序不正确，因为 Jet 没有 12.0 版本。正如@Comintern 在您发布的链接中的评论和答案一样，请使用 ACE 驱动程序版本。但请注意，对于这两种类型，驱动程序的 32/64 位版本必须与您尝试连接到 Excel 数据源的 MS Office 位版本或任何其他程序甚至语言（即 PHP、Python、Java）相匹配。
For older Excel .xls files, you would use Jet as this engine does not yet know of the .xlsx format and so does not recognize that file type:
对于较旧的 Excel .xls 文件，您可以使用 Jet，因为该引擎尚不知道 .xlsx 格式，因此无法识别该文件类型：
strConnection = "Provider=Microsoft.JET.OLEDB.4.0;" _
& "Data Source='C:\Path\To\Excel.xls';" _
& "Extended Properties=""Excel 8.0;HDR=YES;"";"
For more recent Excel files (.xlsx, .xlsm, .xlsb), you would use ACE which is also backwards compatible for .xls types:
对于更新的 Excel 文件（.xlsx、.xlsm、.xlsb），您可以使用 ACE，它也向后兼容 .xls 类型：
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source='C:\Path\To\Excel.xlsx';" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
Alternatively, use ODBC which is the industry application connection layer used by many programs even non-Windows systems to connect to external backend sources. Even open source programming languages maintain ODBC APIs including PHP's PDO, Python's pyodbc, R's RODBC.
或者，使用 ODBC 这是许多程序甚至非 Windows 系统使用的行业应用程序连接层来连接到外部后端源。甚至开源编程语言也维护 ODBC API，包括 PHP 的 PDO、Python 的 pyodbc、R 的 RODBC。
For older source formats:
对于较旧的源格式：
strConnection = "DRIVER={Microsoft Excel Driver (*.xls)};" _
& "DBQ=C:\Path\To\Excel.xlsx;"
For newer source formats:
对于较新的源格式：
strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
& "DBQ=C:\Path\To\Excel.xlsx;"
And the same principles of drivers and bit verisons apply to MS Access .mdb vs .accdb versions.
并且驱动程序和位版本的相同原则适用于 MS Access .mdb 与 .accdb 版本。
