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:\data\Edata.xlsx;extended prope
rties=""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:\data\Edata.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$]") '连接两个数据这⾥是上下的合并
install directory'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语句:什么数据库都可以⽤此操作
web开发基础pdf查询数据
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性别=‘男’,年龄=16where姓名=‘张三‘
删除⼀条数据
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:\data\Edata.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$].姓名"properties文件用什么打开
Range("a2:z100").ClearContents
thinkphp漏洞利用工具guiRange("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:\data\Edata.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:\data\Adata.accdb" '在这⾥就不需要表头了
sql = "select * from [客户信息表] where 城市='天津'" ‘查语句是⼀样的
Range("a2:z100").ClearContents
Range("a2").CopyFromRecordset conn.Execute(sql)
conn.Close
babelEnd Sub
ADO⼯具打开的另⼀种⽅式
Sub Macro2()
Dim cnn As Object
Set cnn = CreateObject("tion")
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
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论