VBA实践·利用ADO操作Excel工作表数据

发布日期：2024-10-14 08:08 点击次数：110

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.ConnectionSet 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 = NothingEnd 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 = NothingEnd 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 = NothingEnd Sub

总结1、使用ADO对象创建数据库连接；2、打开数据库：conn.open方法；3、编写sql语句；4、执行sql语句：conn.Execute ；若为查询语句，则创建记录集对象用于存储查询结果；5、关闭记录集对象、释放空间；6、关闭数据库连接对象，释放空间。

进入公众号👆

★★★查看更多的内容★★★

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。