摘要：
示例：在VBA View宏代码1子宏1（）2“3”宏1宏4“56”7应用程序中记录和导入现有外部数据的宏。CutCopyMode=False8WithActiveSheet.ListObjects。添加（源类型：=0，源：=阵列（_9“OLEDB；提供程序=Microsoft.ACE.OLEDB.12.0；密码=“”；用户ID=
引例：
在VBA里录制导入现有外部数据的宏查看宏代码
  1 Sub 宏1() 2 ' 3 ' 宏1 宏 4 ' 5 6 ' 7 Application.CutCopyMode = False 8 With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _ 9 "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=E:dataEdata.xlsx;Mode=Share Deny Write;Extended Proper" _ 10 , _ 11 "ties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;" _ 12 , _ 13 "Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databas" _ 14 , _ 15 "e Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fal" _ 16 , _ 17 "se;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass User" _ 18 , _ 19 "Info Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _ 20 ), Destination:=Range("$C$5")).QueryTable 21 .CommandType = xlCmdTable 22 .CommandText = Array("data$") 23 .RowNumbers = False 24 .FillAdjacentFormulas = False 25 .PreserveFormatting = True 26 .RefreshOnFileOpen = False 27 .BackgroundQuery = True 28 .RefreshStyle = xlInsertDeleteCells 29 .SavePassword = False 30 .SaveData = True 31 .AdjustColumnWidth = True 32 .RefreshPeriod = 0 33 .PreserveColumnInfo = True 34 .SourceConnectionFile = "C:UserseonDocuments我的数据源Edata data$.od.odc" 35 .SourceDataFile = "E:dataEdata.xlsx" 36 .ListObject.DisplayName = "表_Edata_data_.od" 37 .Refresh BackgroundQuery:=False 38 End With 39 End Sub |
会发现宏代码非常的缀长，实际上并不需要这么多
核心代码：OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:dataEdata.xlsx;
说明：连接别的数据库，这两句就够了
对于Excel还有一个扩展属性：
是否表头：
Extended Properties="HDR=YES;";
标准句式："Provider = Microsoft.ACE.OLEDB.12.0;Data Source=E:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES"""
一、使用ADO连接外部Excel数据源
连接步骤：
1 在VBE界面中 工具—引用
勾选Microsoft ActiveX Data Object x.x Library
2 连接代码
Sub test() Dim conn As New ADODB.Connection conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=D:datadata.xlsx;extended properties=""excel 12.0;HDR=YES""" ‘这里使用SQL对数据进行操作 conn.Close End Sub |
抓取数据：
Range(“a1”).CopyFromRecordset conn.Execute(“select * from [data$]”） |
二、常用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) |
示例代码：
Sub test() Dim conn As New ADODB.Connection Dim sql As String conn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=E:dataEdata.xlsx;extended properties=""excel 12.0;HDR=YES""" '- - - - - - - - - - - - - - - - - - - - - - - - - - - - -分割线 - - - - - - - - - - - - - - - - - - - - - - - - - - - - '抓取前先清除数据 Range("a2:z100").ClearContents '正式步骤 'sql = "insert into [data$] (姓名,性别,年龄) values ('田七','男',33)" 'conn.Execute (sql) sql = "select a.姓名,性别,年龄,月薪 from (select * from [data$] union all select * from [data2$])a left join [data3$] on a.姓名 = [data3$].姓名" Range("a2").CopyFromRecordset conn.Execute(sql) 'CopyFromRecordset 从记录集拷贝 '- - - - - - - - - - - - - - - - - - - - - - - - - - - - -分割线- - - - - - - - - - - - - - - - - - - - - - - - - - - - - conn.Close End Sub |
三、使用ADO连接ACCESS数据库
连接步骤：
1 在VBE界面中 工具—引用
勾选Microsoft ActiveX Data Objects x.x Library
2 连接代码
Sub test() Dim conn As New ADODB.Connection conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:dataAdata.accdb" '这里使用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=E:dataAdata.accdb" '- - - - - - - - - - - - - - - - - - - - - - - - - - - - -分割线 - - - - - - - - - - - - - - - - - - - - - - - - - - - - '抓取前先清除数据 Range("a2:z100").ClearContents '正式步骤 sql = "delete from [客户信息表] where 公司名称='森通'" '在其他数据库，表名不用加$ conn.Execute (sql) ' Range("a2").CopyFromRecordset conn.Execute(sql) 'CopyFromRecordset 从记录集拷贝 '- - - - - - - - - - - - - - - - - - - - - - - - - - - - -分割线- - - - - - - - - - - - - - - - - - - - - - - - - - - - - conn.Close End Sub |
四、课程小结及课后练习
课程小结
需要理解并记住写法的概念
select * from [data$]
CONN.OPEN .EXECUTE .CLOSE
需要理解的概念
ADO与打开文件做操作的差别 和不同的应用场景
理解Ado连接代码的意义
了解SQL语句
课后练习
Excel库存管理系统:
问题：
作业表中完成数据查询
要求：
共2440行数据。