VBA 编码模式:将数据库查询结果导入 Excel
介绍实现将数据库查询结果导入Excel的VBA编码模式。
相关文章: VBA 编码模式:创建 ADO DB 连接字符串
1. Excel画面及操作方法
1.1. Excel屏幕
在单元格“B1”中输入连接字符串以连接到数据库,并在单元格“B2”中输入 SQL 查询以提取数据。
单击“开发工具 > 插入 > ActiveX 控件 > 命令按钮”添加一个按钮,并将名称更改为“执行查询”。
示例屏幕如下。
1.2.如何将数据库查询结果导入Excel
它的工作方式与上图相同。
- 单击添加到 Excel 的“执行查询”按钮
- 来自 VBA 代码的数据库连接
- 在连接的数据库中执行查询
- 返回数据库中执行的查询结果
- 在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 驱动程序 |微软文档
执行以下步骤。
- 右键单击桌面>新建>文本文档
- 将文件名更改为 test.udl 并按回车键
- 双击打开test.udl文件,在“提供者”选项卡中选择一个提供者,在“连接”选项卡中输入服务器、用户名、密码、数据库等,“测试连接”,然后“确定” ” 来拯救。
- ConnectionString可以用编辑器(记事本等)打开保存的test.udl文件得到
![](https://prodskill.com/wp-content/uploads/2022/09/image-191.png)
![](https://prodskill.com/wp-content/uploads/2022/09/image-192.png)
该方法根据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
感谢发帖~自从我关注之后,查询在DB中运行良好,值也保存到其他工作表中。一个问题是,如果查询很长,SQL查询语句不能包含在单元格B2中,我该如何调用查询?我不知道您是否会看到回复,但如果您看到了,我将不胜感激!
感谢您的访问和评论。
如果SQL查询很长,最好将查询内容另存为一个单独的sql文件,然后在VBA代码中导入该文件的内容。
我会在不久的将来单独写一篇关于这个的文章。