VBA之使用ADO操作外部数据

1.添加ADO接口
工具→引用→找到Microsoft ActiveX Data Objects→确定


2.连接ADO
Dim conn As New ADODB.Connection
conn.Open “Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended properties=”“excel 12.0;HDR=YES”""
conn.Close

3.和sql语句搭配使用

代码:
Sub test()
Dim conn As New ADODB.Connection

conn.Open “Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended properties=”“excel 12.0;HDR=YES”""

'Range(“a1”).CopyFromRecordset conn.Execute(“select * from [data$] union all select * from [data2$]unionallselect∗from[data2]”)

'Range(“e1”).CopyFromRecordset conn.Execute(“select 姓名,年龄 from[data$] union all select 姓名,年龄 from[data2$]unionallselect姓名,年龄from[data2]”)

Range(“h1”).CopyFromRecordset conn.Execute(“select * from [data$] where 性别='男'union all select * from [data2$]where性别=′男′unionallselect∗from[data2] where 性别=‘男’”)
conn.Close

End Sub

4.使用sql左连接

代码如下:

Sub test()
Dim conn As New ADODB.Connection
Dim sql As String

conn.Open “Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:\data\Edata.xlsx;extended properties=”“excel 12.0;HDR=YES”""

'Range(“a1”).CopyFromRecordset conn.Execute(“select * from [data$] union all select * from [data2$]unionallselect∗from[data2]”)

'Range(“e1”).CopyFromRecordset conn.Execute(“select 姓名,年龄 from[data$] union all select 姓名,年龄 from[data2$]unionallselect姓名,年龄from[data2]”)

'Range(“h1”).CopyFromRecordset conn.Execute(“select * from [data$] where 性别='男'union all select * from [data2$]where性别=′男′unionallselect∗from[data2] where 性别=‘男’”)
'sql = “insert into [data$] (姓名,性别,年龄) values ('AA','男',33)" 'sql = "update [data$](姓名,性别,年龄)values(′AA′,′男′,33)"′sql="update[data] set 性别=‘男’,年龄=1 where 姓名=‘张三’”

'sql = “select [data$].姓名,性别,年龄,月薪 from [data$].姓名,性别,年龄,月薪from[data] left join [data3$] on [data$]on[data].姓名=[data3$].姓名 " sql = "select a.姓名,性别,年龄,月薪 from (select * from [data$].姓名"sql="selecta.姓名,性别,年龄,月薪from(select∗from[data] union all select * from [data2$])a left join [data3$])aleftjoin[data3] on a.姓名 = [data3$].姓名”
Range(“a2”).CopyFromRecordset conn.Execute(sql)

conn.Close

End Sub