sql - 在 Excel VBA 中使用 SQL 访问其他 Excel 工作簿

Question

我正在尝试编写一个连接字符串和 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

当我尝试打开连接时，我不断收到以下信息。我的连接字符串出了什么问题？

External table is not in the expected format

我找到了Following并将我的代码更改为:

sConnString = "provider=Microsoft.Jet.OLEDB.12.0;data source=" & _
                  "C:\Users\dblois\Desktop\Shareenas Report.xlsx" + ";Extended Properties=Excel 12.0;"

然后我收到以下错误:

Provider cannot be found. It may not be property installed

Answer 1

第一次尝试时，您的 OLEDB 驱动程序不适合 Excel 文件类型。在第二次尝试中，您的 OLEDB 驱动程序不正确，因为 Jet 没有 12.0 版本。正如 @Comintern 在您发布的链接中的评论和答案，请使用 ACE 驱动程序版本。但请注意，对于这两种类型，驱动程序的 32/64 位版本必须与您尝试连接到 Excel 数据源的 MS Office 位版本或任何其他程序甚至语言(即 PHP、Python、Java)相匹配。

对于较旧的 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;"";"

对于更新的 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;"";"

<小时/>

或者，使用 ODBC，这是许多程序(甚至非 Windows 系统)用来连接到外部后端源的行业应用程序连接层。即使是开源编程语言也维护 ODBC API，包括 PHP 的 PDO、Python 的 pyodbc、R 的 RODBC。

对于较旧的源格式:

strConnection = "DRIVER={Microsoft Excel Driver (*.xls)};" _
                  & "DBQ=C:\Path\To\Excel.xlsx;"

对于较新的源格式:

strConnection = "DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" _
                  & "DBQ=C:\Path\To\Excel.xlsx;"

驱动程序和位版本的相同原理适用于 MS Access .mdb 与 .accdb 版本。