VBA-使用ADO操作外部数据

使用ADO连接外部excel数据源

补充小知识：在不打开文件的情况下，抓取数据

1）打开数据-现有连接-浏览更多，然后导入你要导的数据，就能在不打开该文件的条件下，进行透视，操作等。

然后就是通过VBA来实现这个小功能

Sub test1()
MsgBox "叫""张三""的那个人" '想要输出：叫“张三”的那个人,那么需要多加一层“”，将里面的双引号转义
End Sub

1）首先要打开通道

　　在VBA界面中工具引用，勾选 Microsoft ActiveX Data Objects x.x Library ，借此就可以使用ADO通道

2）然后用代码来实现这个通道

Sub test()
Dim conn As New ADODB.Connection '定义一个通道
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES"""
'通过什么方法，连接什么文件，是否有表头

'conn.Execute("select * from [data$]")  '*代表所有的列 表明后面需要加 $，在这里是已经抓取到数据了
Range("a1").CopyFromRecordset conn.Execute("select * from [data$]") '前面Range("a1").CopyFromRecordset的作用是将抓取到的数据放到以“a1”为头的单元格里
　　　　　　　　　　　　　　　　　　　　　　　　　　'SQL语句都在这个双引号里面进行操作
conn.Close
End Sub

3）SQL语句操作表

Sub test()
Dim conn As New ADODB.Connection
Dim sql As String
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES"""
Range("a2:z100").ClearContents
'Range("a1").CopyFromRecordset conn.Execute("select * from [data$] union all select * from [data2$]") '连接两个数据 这里是上下的合并
'Range("a1").CopyFromRecordset conn.Execute("select 姓名,年龄 from [data$] union all select 姓名,年龄 from [data2$]") '如果两个表不一样可以用共名的
'Range("a1").CopyFromRecordset conn.Execute("select 姓名,年龄 from [data$] where 性别='男'") '条件查找 在这SQL语句中可以使用单引号　
'可以使用下面的方法简写
sql = "insert into [data$] (姓名,性别,年龄) values ('田七','男',33)"  '往数据里插入一行数据
conn.Execute (sql) '执行代码
conn.Close
End Sub

常用SQL语句：什么数据库都可以用此操作

查询数据
select * from [data$]
查询某几个字段
select 姓名,年龄 from [data$]
带条件的查询
select * from [data$] where 性别 = "男“合并两个表的数据
select * from [data$] union all select * from [data2$]
插入新纪录
insert into [data$] (姓名,性别,年龄) values ('AA','男',33) 
修改一条数据
update [data$] set 性别=‘男’,年龄=16 where 姓名=‘张三‘
删除一条数据
delete from [data$]  where 姓名='张三'
使用LEFT JOIN …ON…  (类似于VLOOKUP）
select [data3$].姓名,性别,年龄,月薪 from [data$] left join [data3$] on [data$].姓名=[data3$].姓名
先UNION ALL 再LEFT JOIN
select * from (select * from [data$] union all select * from [data2$])a left join [data3$] on a.姓名=[data3$].姓名
'将查询结果赋值到数组
arr = Application.WorksheetFunction.Transpose(conn.Execute("select * from [data$]").GetRows)

left join on方法讲解

Sub test()
Dim conn As New ADODB.Connection
Dim sql As String
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES"""
'sql = "select * from [data$] left join [data3$] on [data$].姓名=[data3$].姓名" '在这里会出个错误，就是有两个姓名列，错在*上
'(select * from [data$]) (left join [data3$] on [data$].姓名=[data3$].姓名) 为方便理解上面为啥错误，上面语句应该这样断
'left是以左边的数据为主，也可以使用right以右边的数据为主
sql = "select [data$].姓名,性别,年龄,月薪 from [data$] left join [data3$] on [data$].姓名=[data3$].姓名"
Range("a2:z100").ClearContents
Range("a2").CopyFromRecordset conn.Execute(sql)
conn.Close
End Sub

先合并两个表，然后再left join

Sub test()
Dim conn As New ADODB.Connection
Dim sql As String
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES"""
'sql = "(select * from [data$] union all select * from [data2$])a" 意思是两个表连接成的新表 名字叫做 a
sql = "select a.姓名,性别,年龄,月薪 from (select * from [data$] union all select * from [data2$])a left join [data3$] on a.姓名=[data3$].姓名"
Range("a2:z100").ClearContents
Range("a2").CopyFromRecordset conn.Execute(sql)
conn.Close
End Sub

使用ADO连接ACCESS数据库

Sub test()
Dim conn As New ADODB.Connection
Dim sql As String
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:dataAdata.accdb"    '在这里就不需要表头了

sql = "select * from [客户信息表] where 城市='天津'"      ‘查找语句是一样的
Range("a2:z100").ClearContents
Range("a2").CopyFromRecordset conn.Execute(sql)
conn.Close
End Sub

ADO工具打开的另一种方式

Sub Macro2()
    Dim cnn As Object
    Set cnn = CreateObject("adodb.connection")
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1;hdr=no';Data Source=" & ThisWorkbook.Path & "Book2.xls"
    [a1].CopyFromRecordset cnn.Execute("[Sheet1$]")
    cnn.Close
    Set cnn = Nothing
End Sub