VBA学习(48)：利用ADO操作Excel工作表数据

VBA可以利用ADO创建SQL，从而实现在不打开Excel工作簿的情况下，对工作表的数据进行读写等操作。对于数据量比较大的Excel工作簿，利用ADO模型可以大大提高性能。

案例数据

D盘有ExcelSQL数据库.xlsx工作簿，工作簿内有【员工信息】工作表，其字段包含工号、姓名、部门、性别和年龄。

使用ADO的两种方式

1、CreateObject函数创建

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

2、外部引用

VBE界面上选择：工具-引用-Microsoft ActiveX Data Object x.x Library

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

 连接数据源（Excel工作簿）

conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"

 增加记录

Sub 增加记录()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"

    Dim sql As String
    sql = "insert into [员工信息$] values  ('999','VBAMatrix','开发部','男',19)"
    conn.Execute (sql)
    
    sql = "insert into [员工信息$](工号,姓名,部门,性别) values ('666','PythonMatrix','开发部','女')"
    conn.Execute (sql)
    
    sql = "insert into [员工信息$a1:e65536] values ('888','SQLMatrix','开发部','女',30)"
    conn.Execute (sql)

    conn.Close
    Set conn = Nothing
End Sub

 注:(1)SQL语句中的表可以是工作表[员工信息$]，或工作表的部分区域[员工信息$a1:e65536] ;(2)目标字段数量和values值的数量要一致。

删除记录

不支持，会报错。

更新记录

Sub 更新记录()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")
    
    conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"
    Dim sql As String
    dep = InputBox("更新张三所在的部门")
    sql = "update [员工信息$] set 部门='" & dep & "' where 姓名='张三'"
    conn.Execute (sql)
    
    conn.Close
    Set conn = Nothing
End Sub

注：变量在sql语句中要保留单引号。

查询记录

Sub 查询男性员工并写入新的工作表()
    Dim conn As Object
    Set conn = CreateObject("ADODB.Connection")

    conn.Open "provider=microsoft.ace.oledb.12.0;extended properties='excel 12.0';data source='D:\ExcelSQL数据库.xlsx'"

    Dim sql As String
    sql = "select * from [员工信息$] where 性别='男'"
    
    '创建记录集对象
    Dim rs As ADODB.Recordset
    Set rs = conn.Execute(sql)
    
    '将记录集的数据写入工作表中,获取字段名
    Dim i As Integer
    For i = 0 To rs.Fields.Count - 1
        ThisWorkbook.Sheets("男性员工").Cells(1, i + 1) = rs.Fields(i).Name
    Next
    
    ThisWorkbook.Sheets("男性员工").Range("a2").CopyFromRecordset rs
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

总结

1、使用ADO对象创建数据库连接；

2、打开数据库：conn.open方法；

3、编写sql语句；

4、执行sql语句：conn.Execute ；若为查询语句，则创建记录集对象用于存储查询结果；

5、关闭记录集对象、释放空间；

6、关闭数据库连接对象，释放空间。

 技术交流，软件开发，欢迎微信沟通：