把Excel当桌面数据库使用（一）——在VBA中使用

在VBA中使用ADODB访问Excel
1、创建工作表备用
新建工作薄文件data.xlsx，修改表”sheet1“名为”Employee“，在Employee中创建结构如下的工作表并输入数据：
id name gender age
1 张三 男 22
2 李四 女 20
3 王五 男 21

2、引用ADODB6.0
”开发工具“-”Visual Basic“，在VBAProject右键”插入-模块“。
在”工具“中添加引用Microsoft ActiveX Data Objects 6.1 Library
若未显示”开发工具“菜单，则从”文件“-”选项“-”自定义功能区“中勾选。

3、Excel桌面数据库在VBA中使用
新建过程test

Sub test()

'定义ADODB连接对象conn和游标对象rs
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

'定义连接字符串connStr
Dim connStr As String

'定义变量pathStr保存工作薄文件路径
 Dim pathStr As String
 
 '定义字符串sql
 Dim sql As String
 
 '定义字符串变量msg，存储输出信息
 Dim msg As String
 
  
 ' 获取data.xlsx所在完整路径并保存在变量pathStr中
 pathStr = ThisWorkbook.FullName

 '创建连接对象conn
 Set conn = CreateObject("ADODB.Connection")
 
 ' 创建游标对象rs
 Set rs = CreateObject("ADODB.Recordset")
 
Select Case Application.Version * 1 - -不同版本的excel要用不同的连接串
    Case Is <= 11
       connStr = "Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=" & pathStr
    Case Is >= 12
        connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & pathStr & ";Extended Properties=""Excel 12.0;HDR=YES"";"""
End Select

' conn对象打开相应的Excel连接
conn.Open connStr

'sql语句，注意Excel中表名要用[]括起来，并用要在名称后加$
sql = "select [id],[name],[gender],[age] from [Employee$]"

'游标对象rs通过sql语句打开conn连接，获取查询数据
rs.Open sql, conn, 1, 1

'msg变量初始化为空字符串
msg = ""

' 利用Do While循环获取所有信息
Do While Not rs.EOF()
   '用rs(字段名).value读取每一条记录相关信息，利用字符串连接符&连接在一起，每一条记录换行chr(13)
   msg = msg & "ID:" & rs("id").Value & "," & "name:" & rs("name").Value & "," & "gender:" & rs("gender").Value & "," & "age:" & rs("age").Value & Chr(13)
   '游标指向的当前记录下移
   rs.MoveNext
Loop

'利用msgbox输出信息
MsgBox (msg)

'关于对象
rs.Close
conn.Close

End Sub

4、运行
单击运行即可看到效果