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、关闭数据库连接对象,释放空间。
技术交流,软件开发,欢迎微信沟通: