有多种方法可以将外部源的数据放入 Excel 工作表中。在本文中,我们将了解如何使用 VBA 中的 SQL 从 MS Access 数据库文件和 Excel 文件中查询数据。是的,我们将把一些 SQL 与 VBA 混合起来!而且它比您想象的要好!
SQL 代表结构化查询语言,是一种用于从数据库检索信息的语言(例如MS Access、MS SQL Server、Oracle、Sybase、SAP、MySQL 等)。
您还可以从 Internet、文本文件或其他 Excel 或 CSV 文件获取数据。
因此,我们需要一个数据库连接(下面宏中的变量 varConn)和一个 SQL 查询(变量 varSQL)来自动从数据库中检索报告的数据。下面的示例是一个 SQL 查询,它从 MS Access 中的小型数据库检索数据。
单击 下载 MS Access 数据库。 MS Access 文件将如下所示:
让我们编写将执行 SQL 查询的宏。
打开菜单服务 - 宏 - 编辑器 Visual Basic, insert 一个新模块(插入 - 模块菜单)并将宏文本复制到其中:
Sub SQLQuery_1() Dim varConn As String Dim varSQL As String Range("A1").CurrentRegion.ClearContents varConn = "ODBC;Driver={Microsoft Access Driver (*.mdb, *.accdb)}" varSQL = "SELECT Month, Product, City FROM Sumproduct" With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1")) .CommandText = varSQL .Refresh BackgroundQuery:=False End With End Sub
单击保存并返回Excel。在菜单视图 - 宏 (Alt+F8) 中选择宏的名称“SQLQuery_1”。将出现一个对话框,单击数据库...,查找我们的 Access 文件并单击确定。
现在我们可以看到,宏查询已将 Sumproduct 表中的 Month、Product 和 City 列从 test.mdb 数据库提取到 Excel 文件中。
类似地,我们可以从常规 Excel 文件中获取数据,就像从数据库中一样。为此,我们需要稍微重写宏代码,并将连接驱动程序从 Access 更改为 Excel。
我们将 Sumproduct 表从 Access 导出到 Excel 并保存文件。接下来,我们将更改后的代码插入到新模块中并运行宏:
Sub SQLQuery_2() Dim varConn As String Dim varSQL As String Range("A1").CurrentRegion.ClearContents varConn = "ODBC;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}" varSQL = "SELECT Month, Product, City FROM Sumproduct" With ActiveSheet.QueryTables.Add(Connection:=varConn, Destination:=Range("A1")) .CommandText = varSQL .Refresh BackgroundQuery:=False End With End Sub
将出现一个窗口,我们可以在其中选择 Excel 文件(应名为 Sumproduct.xlsx 或具有不同的扩展名)。我们找到并选择它。单击“确定”,我们将获得与 Access 文件中相同的数据。