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