VBA 编码模式：将数据库查询结果导入 Excel

介绍实现将数据库查询结果导入Excel的VBA编码模式。

相关文章： VBA 编码模式：创建 ADO DB 连接字符串

1. Excel画面及操作方法

1.1. Excel屏幕

在单元格“B1”中输入连接字符串以连接到数据库，并在单元格“B2”中输入 SQL 查询以提取数据。

单击“开发工具 > 插入 > ActiveX 控件 > 命令按钮”添加一个按钮，并将名称更改为“执行查询”。

示例屏幕如下。

1.2.如何将数据库查询结果导入Excel

它的工作方式与上图相同。

1. 单击添加到 Excel 的“执行查询”按钮
2. 来自 VBA 代码的数据库连接
3. 在连接的数据库中执行查询
4. 返回数据库中执行的查询结果
5. 在excel表中显示查询结果

2.选择数据库连接的提供商

“2。这是“VBA 代码中的数据库连接”中使用的提供程序。它可以指定为 ADO.Connection 对象的 Provider 属性或 ConnectionString。

通常指定为ConnectionString，示例如下。

• 用于 ODBC 驱动程序的 MS OLE DB 提供程序
  • 供应商=MSDASQL.1;密码=[密码];坚持安全信息=真；用户 ID=[UserID]; Data Source=[ODBC控制面板中设置的DSN]；初始目录 = [数据库名称]
• 用于 SQL Server 的 MS OLE DB（SQL Server 身份验证）
  • 供应商=SQLOLEDB.1；密码=[密码];坚持安全信息=真；用户 ID=[UserID]; Initial Catalog=[DB name];Data Source=[Server address (IP or host name)]
• 用于 SQL Server 的 MS OLE DB（Windows 身份验证）
  • 供应商=SQLOLEDB.1；密码=[密码];集成安全性=SSPI；坚持安全信息=假；用户 ID=[UserID]; Initial Catalog=[DB name];Data Source=[Server address (IP or host name)]
• 用于 OLE DB 的 Oracle 提供程序
  • Provider=OraOLEDB.Oracle.1;Password=[密码];坚持安全信息=真；用户 ID=[UserID]; Data Source=[服务器地址（IP或主机名）]

如果PC上安装了合适的Provider，可以连接Oracle、MS SQL Server、MySQL、MariaDB、PostgreSQL等RDBMS服务器，也可以连接MS Access、SQLite等本地RDBMS。

ConnectionString 可以通过以下方式创建。

如何创建 ConnectionString 1. 使用 UDL 文件

通用数据链接 (UDL) 文件是包含在 Microsoft 数据访问组件 (MDAC) 中的一个概念。

参考： 配置通用数据链接 (UDL) – SQL Server 的 OLE DB 驱动程序 |微软文档

执行以下步骤。

1. 右键单击桌面>新建>文本文档
2. 将文件名更改为 test.udl 并按回车键
3. 双击打开test.udl文件，在“提供者”选项卡中选择一个提供者，在“连接”选项卡中输入服务器、用户名、密码、数据库等，“测试连接”，然后“确定” ” 来拯救。
4. ConnectionString可以用编辑器（记事本等）打开保存的test.udl文件得到

该方法根据Windows操作系统的32位或64位平台显示Provider。也就是说，在 64 位操作系统中只显示 64 位提供程序。如何在 64 位操作系统上显示 32 位 Provider 将在另一篇文章中发布。

如何创建ConnectionString 2.VBA代码

上一篇 VBA 编码模式：创建 ADO DB 连接字符串 已经详细解释了总之。

• 添加了 Microsoft ActiveX 数据对象 2.8 库参考
• 添加了 Microsoft OLE DB 服务组件 1.0 类型库参考
• 使用以下代码创建一个 ConnectionString

Private Sub btnBuildConnectionString_Click()
On Error GoTo 0

    Dim cn As ADODB.Connection, MSDASCObj As MSDASC.DataLinks, oCurrentRange As Range
    Dim eOrgXlEnableCancelKey As XlEnableCancelKey

    Set oCurrentRange = ActiveCell

    Set MSDASCObj = New MSDASC.DataLinks

    eOrgXlEnableCancelKey = Application.EnableCancelKey
    Application.EnableCancelKey = xlDisabled
    Set cn = New ADODB.Connection
    cn.ConnectionString = oCurrentRange
    If MSDASCObj.PromptEdit(cn) = True Then
        oCurrentRange = cn.ConnectionString
    End If

    Set cn = Nothing
    Set MSDASCObj = Nothing

    Application.EnableCancelKey = eOrgXlEnableCancelKey
End Sub

提供者相关的预防措施

要使用的Provider和Excel的32位和64位平台必须相同。如果您使用的是 32 位的 Excel，则必须使用 32 位的 Provider，如果您使用的是 63 位的 Excel，则必须使用 64 位的 Provider。

在使用 64 位 Windows 操作系统和 32 位 Excel 的情况下，当尝试使用第一种方法中创建的 ConnectionString 连接到数据库时，可能会出现“找不到提供程序”的错误。

这种情况可以通过检查 Excel 和提供商的平台是否相同并使用合适的提供商来解决。

三、DB Query结果导入Excel的实现

3.1.添加参考

在 VBA 代码编辑器菜单中执行“工具 > 添加引用”并添加“Microsoft ActiveX 数据对象 2.8 库”引用。

3.2.将数据库查询结果导入 Excel 的 VBA 代码

在添加到 Excel 工作表的按钮的点击事件过程中编写如下代码。

Private Sub cmd_RunQuery_Click()
    Dim sSQL As String
    Dim oCon As ADODB.Connection, oRS As ADODB.Recordset
    Dim sConnectionString As String

    sConnectionString = Range("B1").Value2
    sSQL = Range("B2").Value2

    Set oCon = New ADODB.Connection
    Set oRS = New ADODB.Recordset
    oCon.ConnectionTimeout = 10
    oCon.Open sConnectionString
    oCon.CommandTimeout = 60

    oRS.Open sSQL, oCon, adOpenForwardOnly, adLockReadOnly
    
    Dim oSht As Worksheet, lColIdx As Long
    Set oSht = Worksheets.Add(After:=Me)
    For lColIdx = 0 To oRS.Fields.Count - 1
        oSht.Range("A1").Offset(0, lColIdx).Value2 = oRS.Fields(lColIdx).Name
    Next lColIdx
    oSht.Range("A2").CopyFromRecordset oRS

    oRS.Close: Set oRS = Nothing
    oCon.Close: Set oCon = Nothing
End Sub

上面代码的解释如下。

• 第 6 行：获取 Excel 工作表“B1”单元格的值作为 sConnectionString 变量。
• 第 7 行：将 Excel 工作表的“B2”单元格的值导入到 sSQL 变量中。
• 第 12 行：通过将 sConnectionString 值传递给 ADODB.Connection 对象 oCon 来连接到数据库。
• 第 15 行：对数据库执行 SQL 查询并将结果放入 ADODB.Recordset 对象 oRS。
• 第 18 行：创建一个 sheet oSht 来显示 SQL Query 执行结果。
• 第19-21行：输出sheet oSht第一行对象oRS的字段（列）列表。
• 第 22 行：在 oSht 表的“A2”单元格中输出 oRS（SQL 查询执行结果）的内容。

3.3.下载示例文件

您可以在下面的 github 链接中查看包含上述代码的示例 Excel 文件。
文件链接： github DAToolset/VBACode/VBA 编码模式 - DB Query1.xlsm