Excel VBA 编程 -- 访问数据库
- 2026-04-06 17:47:35
Excel VBA 编程 -- 访问数据库上文我们提到，OLE DB 是微软继 ODBC 之后又一项数据访问技术。OLE DB 基于 Windows 的 COM 组件技术，为了适配于各种语言，OLE DB 提供了双接口： 虽然如此，但直接访问 OLE DB 还是比较麻烦的。为了简化对 OLE DB 的访问，微软又推出了 ActiveX Data Objects，简称 ADO。ADO 以对象模型呈现编程接口，向用户隐藏了 OLE DB 的技术细节。ADO 推出后，取得了巨大成功，以至于后续的 .NET 平台的数据库访问技术仍然沿用了 ADO 这一名称，后缀以 .NET 以示区分。 ADO 首先在 VB/VBA/VBScript 上取得了成功。现在虽然 VB 以及 VBScript 不再是人们的首选语言，但 Office 内嵌的 VBA 仍然是从事 Office 编程的人们每天都要使用的语言工具。 今天我们就来讨论如何使用 VBA 访问数据库。 首先，创建一个数据库表，名为 MathScores： 
我们用这张表记录学生的数学成绩，样本数据如下： 
其中，里斯本由于某种原因没有参加考试，因此没有成绩。 现在，数据库端已经准备好，下面来看如何将这份成绩单读到 Excel 工作表中。 要想在 Excel/VBA 中访问数据库，首先必须引入 ADO。打开 Visual Basic for Applications 编辑器，点击“工具 (T)”菜单，选择“引用 (R)......”，打开“引用”窗体，如图所示： 
在“可使用的引用 (A)”列表框中，选中 Microsoft ActiveX Data Objects 6.1 Library，点击“确定”关闭窗体。 至此，第一步引入 ADO 就完成了。下面开始写代码。 写代码的第一步是插入模块，我们的代码都是写在模块里的。 点击“插入 (I)”菜单，选择“模块 (M)”，即可插入模块。 双击左侧“工程”列表框里的模块名称（通常为 Module1），在右侧编辑框中就可以写代码了。本例中，我们把从数据库读取数据的过程命名为 ReadDataFromDb，如图所示： 
程序一开始，首先是声明一些变量。在 VBA 中，将变量声明为具体的类型，如本例中的 Connection，String，Integer 等，一是有助于 VBA 的编译器进行类型错误检查，代码中变量的使用如果与其声明类型不符，编译器就会报错。第二是提高代码的执行效率。 如果像这样声明变量： 则 MyVar 变量将作为变体类型对待（关于变体类型，请参阅文末的相关阅读）。变体类型一方面类型错误不容易发现，另外其执行效率也是所有数据类型中最低的。 回到我们的例子。接下来是构造连接字符串： 连接字符串构造好后，接下来是创建两个对象。一个是 Connection 对象，这是访问数据库时使用的第一个对象，表示与数据库的一个连接。只有与数据库建立连接以后，才能使用后续的对象访问数据库的数据。创建 Connection 对象之后，调用该对象的 Open 方法，将前面构造的连接字符串作为参数，尝试打开与数据库的连接。 一般来说，访问数据库时，与数据库建立连接是最容易出错的步骤，原因多种多样，有网络问题（如果不是本机数据库），有连接字符串的问题（譬如数据库名称、用户名、密码等错误），也有数据库服务器设置的问题，等等，不一而足。 如果成功建立与数据库的连接，则接着创建 Recordset 对象，这是获取数据的主要对象。Recordset 对象需要两个参数：一个与数据库的连接，Recordset 借此与数据库打交道，一个是 SQL 语句，Recordset 借此获取数据。有了这两个参数，就可以调用 Open 方法来获取数据。 如果说建立与数据库的连接是最容易出错的步骤，那么调用 rs.Open 则是第二容易出错的步骤。这一步的错误主要是 SQL 语句的错误，譬如 SQL 语句的语法错误，字段名错误，表名错误，等等。 rs.Open 成功之后，数据已经在记录集 rs 中了，接下来是将这些数据写到 Excel 中，这就是下面这段代码的工作： 
数据存在于记录集 rs 中，我们需要遍历这个记录集，从而把记录集中的数据都读取出来。遍历记录集的方法是使用如下的 While 循环： rs.EOF 的意思是已经读到记录集的末尾了。所以 While 循环的意思：如果还没有读到记录集的末尾，就通过 rs.MoveNext 一条记录一条记录地读，直到记录集的末尾，结束循环。 在读取记录的过程中，我们用 total 来记录总的记录数，用 n 来记录有效的记录数（所谓有效记录，是指有成绩的记录），用 sum 来记录成绩的总和。我们用下面的方法判断是否有效记录： IsNull(rs("Score")) 判断 rs("Score") 字段是否为空。我们上面已经说过，如果由于某种原因没有参加考试，则考试成绩为空。如果成绩为空，则视为无效记录，反之，则为有效记录。 While 循环结束之后，所有姓名/成绩都已经写到 Excel 中，并且记录下了总的记录数、有效记录数、总成绩，此时，记录集 rs 和连接 cn 都不再需要，所以要关闭它们。 接下来将这些统计信息写入 Excel： 
最后，Excel 中的结果如下： 
以上是从数据库中读取数据，下面讨论向数据库写入数据。 将上面 Excel 工作表中的学生成绩数据写入 test 数据库。为了与原来的数据库表 MathScores 相区别，我们另外创建一个与 MathScores 相同结构的表，名为 MathScoresX。 我们要写入的数据取自 Excel 工作表中的第 2 到第 8 行，就是 A2:B8 这个区域的内容。写入数据的方法是：根据工作表的数据构造 SQL 语句，然后在 ADO 中执行此 SQL 语句。我们要构造的 SQL 语句格式如下： 需要构造的就是括号部分。代码如下： 
需要注意的是，在构造 SQL 语句的过程中，需要判断当前单元格是否为空，为空意味着对应的数据库表的字段也为空，这个为空的情况需要特殊处理，如上面代码的注释所示。 SQL 语句构造好之后，调用 ExecuteSQL 过程执行，即可完成向数据库写入数据的工作。 ExecuteSQL 过程的代码如下： 
与从数据库中读取数据不同，写入数据需要创建 Command 对象，这个对象负责执行 SQL 语句，它需要三个参数：打开的连接，SQL 语句，当前要执行的 SQL 种类，adCmdText 表示当前要执行的是一段 SQL 语句。 执行完后，关闭与数据库的连接。 Excel 读取/写入数据库有很多种方法，这里讨论的只是其中之一。 Excel 是处理数据的有力工具，数据库是管理大规模数据的有力工具。Excel 的数据处理能力 + 数据库的数据管理能力 + VBA 的编程能力 = 强有力的生产力工具。 相关阅读 ODBC (5) -- 走向 OLE DB Delphi 编程 -- 谈谈变体类型 Delphi 编程 -- 再谈变体类型（操作 Excel）
虚函数表（vtable）：用于 C/C++ 等的高速访问 自动化接口（IDispatch）：用于 VBA/VBScript 等脚本类语言的访问
Dim MyVarProvider=MSOLEDBSQL19：表示 ADO 连接数据库使用 Microsoft OLE DB Driver 19 for SQL Server 这个最新版本的 OLE DB 提供者。 可以将 ADO 看成 OLE DB Driver 外面包裹的一层外衣，ADO 将 OLE DB Driver 的功能以 Connection、Command、Recordset 等对象的形式呈现给应用程序员，ADO 内部会将应用程序员对这些对象的操作转换为对 OLE DB Driver 具体接口的调用。所以，构造 ADO 的连接字符串时需要指明 ADO 内部要使用哪个 OLE DB Driver。这就是 Provider 关键字的作用。 Integrated Security=SSPI：将 Integrated Security 设置为 SSPI 表示访问数据库时使用 Windows 认证。 Trust Server Certificate=true：表示客户端信任 SQL 服务器的证书。 Data Source=lpc:(local)：表示使用本机数据库，lpc: 表示使用客户端与 SQL Server 的通信共享内存协议。对于本机数据库来说，使用共享内存要比使用 TCP/IP 协议效率高得多。 Initial Catalog=test：表示访问 test 数据库。
WhileNot rs.EOF// 读取数据rs.MoveNext// 下一条记录Wend
If Not IsNull(rs("Score")) Then// ...End If
INSERT INTO MathScoresX(Name, Score) VALUES (...), (...), ...
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
