一、使用ADO连接外部Exce数据源
1 在VBE界面中 工具—引用
勾选Microsoft ActiveX Data Object x.x Library
2 连接代码
代码壳子
Sub test()
Dim conn As New ADODB.Connection
Dim SQL As String
' 打开连接
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\Edata.xlsx;extended properties=""excel 12.0;HDR=YES"""
'可以将通用的SQL语句放在这里(增删改查连接)
SQL = "select * from [Sheet1$] union all select * from [Sheet2$]"
'这里使用SQL对数据进行操作
'抓取数据:
1.有返回的数据
Range("a1").CopyFromRecordset conn.Execute(SQL)
2.没返回的,如删除,增加
CopyFromRecordset conn.Execute(SQL)
conn.Close '关闭连接
End Sub
例如
Sub test()
Dim conn As New ADODB.Connection
Dim SQL As String
' 打开连接
conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\Edata.xlsx;extended properties=""excel 12.0;HDR=YES"""
'可以将通用的SQL语句放在这里(增删改查连接)
'select * from [Sheet1$] where 性别 = '男'
'insert into [Sheet1$] (姓名,性别,年龄) values ('AA','男',33)
'update [Sheet1$] set 性别=‘男’,年龄=16 where 姓名=‘张三‘
'delete from [Sheet1$] where 姓名='张三'
'先 UNION ALL 再 LEFT JOIN
'select * from (select * from [Sheet1$] union all select * from [Sheet2$]) as a left join [Sheet3$] on a.id=[Sheet3$].id
'将查询结果赋值到数组
'arr = Application.WorksheetFunction.Transpose(conn.Execute("select * from [Sheet1$]").GetRows)
全连接、右连接、左连接
我理解的左右连接,即以哪一张表为主
表1 left join 表2 (以表1为主)
表1 right join 表2 (以表2为主)
'select [Sheet3$].学号,姓名,性别,年龄,月薪 from [Sheet1$] left join [Sheet3$] on [Sheet1$].学号=[Sheet3$].学号
'合并两个表的数据union all(两张表上下连接诶)
SQL = "select * from [Sheet1$] union all select * from [Sheet2$]"
'这里使用SQL对数据进行操作
'抓取数据:
Range("a1").CopyFromRecordset conn.Execute(SQL)
conn.Close '关闭连接
End Sub
注意:
1.VBA中双引号," "" "" "
上面的表示相当于, " " " "
转义的意思
2. Provider = Microsoft.ACE.OLEDB.12.0 提供者
3. Data Source= 数据路径
4. HDR=YES表示是否包含表头
在解释一下连接
- 左连接 left:保留第一个数据框X的所有观测。(最常用)
- 右连接 right:保留第二个数据框Y中的所有观测
- 全连接 full:保留两个数据框中的所有观测(并集)
二、使用ADO连接ACCESS数据库
连接代码
Sub test()
Dim conn As New ADODB.Connection
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0 ; Data Source=C:\data\Adata.accdb"
'可以将通用的SQL语句放在这里(增删改查连接)
SQL = "select * from [客户信息表] where 城市='天津' "
'这里使用SQL对数据进行操作
'抓取数据:
1.有返回的数据
Range("a1").CopyFromRecordset conn.Execute(SQL)
2.没返回的,如删除,增加
CopyFromRecordset conn.Execute(SQL)
conn.Close
End Sub