Excel - 使用VBA通过ADO数据库连接来操作一个Excel数据源

Excel - 使用VBA通过ADO数据库连接来操作一个Excel数据源

引用

CSDN

1.

https://blog.csdn.net/guoqx/article/details/126328983

在处理大量数据时，将Excel工作簿作为数据库来操作可以大大提高效率。本文将详细介绍如何使用VBA通过ADO数据库连接来操作Excel数据源，包括具体步骤和代码示例。

你可以将Excel工作簿作为一个完整的数据库来阅读，也就是说，Excel文件可以作为数据库。你可以把行和列的范围作为你的数据库的表，也就是Excel工作簿。

这是Excel提供的一个非常关键的功能，因为想象一下，如果你的Excel文件包含非常巨大的数据，比如说超过10000x500个单元格的数据，你必须根据一些条件从整个工作簿中获取一些数据。是的，你可以按行或按列浏览整个工作表，但要花大量时间浏览每个单元格。这就是为什么我们需要有数据库和表的概念。

读取Excel工作簿作为DataBase的步骤如下：

1. 在你的Excel文件中存储数据，并将其保存在系统中的某个位置，例如 "DB Data.xlsx"
2. 使用微软Excel驱动将Excel文件作为ODBC源（命名为MyExcelDS），此处有单独另一篇文章介绍。
3. 打开一个新的excel文件，你将从 "DB Data.xlsx "中获取数据。
4. 打开VB编辑器
5. 创建一个 "ADODB.Connection "对象
6. 打开连接并提供与步骤2中提供的相同的DSN名称（在本例中是 "MyExcelDS"）。
7. 编写获取数据的查询，提供 "DB Data.xlsx "中的表名作为范围。
8. 执行查询并将结果存储在resultSet中。
9. 从结果集中读取数据并显示在新创建的Excel中，关闭结果集。
10. 可以将此VBA函数指定一个按键，方便执行。

Excel中的数据输入如下：

然后我们打开一个Excel文件，Alt+F11呼出VBE编辑器，Insert一个module，然后输入下面代码：

Sub ReadDB()
    Dim mainWorkBook As Workbook
    Dim intRowCounter
    Set mainWorkBook = ActiveWorkbook
    intRowCounter = 2
    mainWorkBook.Sheets("Sheet2").Range("A2:Z100").Clear
    Set Connection = CreateObject("ADODB.Connection")
    Connection.Open "DSN=MyExcelDS"
    strQuery = "SELECT * FROM [Sheet1$A1:Z500] where Dept = 'IT'"
    Set resultSet = Connection.Execute(strQuery)
    Do While Not resultSet.EOF
        mainWorkBook.Sheets("Sheet2").Range("A" & intRowCounter).Value = resultSet.Fields("Emp Id").Value
        mainWorkBook.Sheets("Sheet2").Range("B" & intRowCounter).Value = resultSet.Fields("Name").Value
        mainWorkBook.Sheets("Sheet2").Range("C" & intRowCounter).Value = resultSet.Fields("Age").Value
        mainWorkBook.Sheets("Sheet2").Range("D" & intRowCounter).Value = resultSet.Fields("Dept").Value
        intRowCounter = intRowCounter + 1
        resultSet.MoveNext
    Loop
    resultSet.Close
End Sub

用这些代码，用来使用VBA创建一个ADO数据库连接，访问我们使用Excel文件定义的ODBC数据源。

然后显示结果如下：

参考：

• VBA-Excel: Read Excel WorkBook as DataBase using ODBC Source
• VBA-Excel: Make Excel File as ODBC Source(Database) using Microsoft Excel Driver