Excel VBA+ADO+SQL.docx
- 文档编号:4127680
- 上传时间:2022-11-28
- 格式:DOCX
- 页数:53
- 大小:64.33KB
Excel VBA+ADO+SQL.docx
《Excel VBA+ADO+SQL.docx》由会员分享,可在线阅读,更多相关《Excel VBA+ADO+SQL.docx(53页珍藏版)》请在冰豆网上搜索。
ExcelVBA+ADO+SQL
ExcelVBA+ADO+SQL入门教程001:
认识SQLInExcel
那就先说SQL吧。
SQL是一种结构化查询语言(StructuredQueryLanguage),是一种声明式语言,敲黑板划重点【结构化和声明式】。
SQL的核心是对表的引用,声明你想从数据源中获取什么样的结果,而不用告诉计算机如何才能够得到结果——
后面这句话似乎很难理解,举例来说,倘若我们需要获取上图所示表格(Sheet1)成绩大于等于80分的人员名单,如果用命令式程序语言,比如VBA,是这样的:
SubMyFind()
Dimarr,brr,i&,k&
arr=Sheet1.[a1].CurrentRegion
ReDimbrr(1ToUBound(arr),1ToUBound(arr,2))
Fori=1ToUBound(arr)
Ifarr(i,2)>=80Then
k=k+1
brr(k,1)=arr(i,1)
brr(k,2)=arr(i,2)
EndIf
Next
[d:
f].ClearContents
[d1].Resize(k,2)=brr
EndSub
你需要通过VBA编程告诉计算机每一步怎么走,数据从哪里来,从哪里开始遍历,行列是多少,符合条件的数据装入哪里,怎么装等等……
而如果用声明式SQL语言呢?
只要告诉计算机我要什么就可以了。
SELECT姓名,成绩FROM[Sheet1$]WHERE成绩>=80
我要Sheet1表(FROM[Sheet1$])……成绩大于等于80(WHERE成绩>=80)……姓名和成绩的数据(SELECT姓名,成绩)。
只要结果,不问过程。
就酱紫的声明式霸道总裁范。
4.为什么要学习SQLInExcel(Excel支持的SQL语言)呢?
换言之,相比于Excel其它功能,例如函数、VBA、POWERPIVOT等,SQL有何优势?
首先,必须严肃脸说明的是,对于普通Excel使用者而言,VBA、SQL以及以后提及的ADO并不是非学不可的,非学不可的是基础操作、函数、透视表、图表……
然而大数据时代,对于另外相当一部分表族而言,Excel用久了,慢慢的,会意识到一个大问题;曾经在你心中无比强大的Excel函数,原来只适合小数据的腾挪躲闪;当数据量稍大后,函数这货就像未嗑士力架的姚明——不来劲的很哩。
SQLInExcel则可以解决函数处理大数据效率低下的问题,嗯~使用SQL语言,你甚至可以将Excel作为前台数据管理界面,数据库(例如ACCESS)作为后台数据储存仓库,进而储存、分析、管理远超Excel体积的数据量。
打个响指,我们之前讲过,VBA处理数据的核心是数组+字典,倘若SQL和它比较起来有何优劣?
作为一个正努力成为乐观主义者的人,星光还是先说优点吧。
通过上面代码的栗子我们很直观的看到,SQL的书写要比VBA编程简洁的多,甚至比小巧灵的函数还要简洁;此外,SQL高效处理的数据量上限,也是远远大于VBA数组+字典的;字典装上50W的数据,一般电脑的计算效率就开始垂直下降了,而SQL还是风轻云淡脸;最后,SQL+ADO+VBA可以通过Excel直接处理数据库(例如ACCESS)来源的数据……。
然后说劣势。
SQL作为一种数据库结构化查询语言,对表的结构和数据的类型有着严格的要求,而严格来说Excel并非数据库,尽管它支持ADO和SQL(谁说装了数据就是数据库的?
拉出去自弹小丁丁500下,好冷)。
Excel对表的结构和数据的类型并没有严格的限定,例如合并单元格,多行表头,空记录,一列之内存在多种数据类型等等劣迹存在,因此,字典+数组处理EXCEL数据的灵活性要远远高于SQL,毕竟数组遍历在手,天下我有,什么合并单元格多行表头,统统都是浮云……
最后,SQLInExcel和PowerBIForExcel(以下简称PowerBI)相比优势在哪里?
从Excel的角度讲,SQL和PowerBI最大的优势是,SQL支持VBA语言。
通过ADO执行SQL语言,VBA可以获取、分析、管理多种来源的数据,甚至进而对获取的数据再搭配字典、数组以及各种Excel自带的功能作进一步自动化、智能化处理……换句话说,VBA运行SQL语句后,可以再整合Excel所有的功能进一步处理数据,除了PowerBI——是的,PowerBI不支持VBA,耸肩,无奈。
从数据的角度讲,PowerBI是一款数据分析的软件,包含了M和DAX查询语言,SQL则是一种数据管理的语言。
查询和管理有何不同?
简而言之,SQL不但可以查询数据,还可以操纵数据,例如增、改、删等等。
而M和DAX语言对数据则只能查询,不能操纵。
就像我们在PowerBI入门教程中讲的,它只能改变自己,永远无法改变对方(指的是数据源,不是我们的爱情)
另外,SQL是一门广被接受和支持的语言。
Excel,ACCESS,R,Python,JAVA,C等等软件和语言,均是支持SQL的;而POWERBI显然没有这样的待遇。
我们很久以说,作为一名数据分析员有三个必须掌握的技能,SQL获得数据,EXCEL分析数据,PPT展现数据。
POWERBI出现后,有人说学了POEWRBI,就不用学SQL了。
如果你能意识到两者之间的不同,显然就会明白这是低头说话不看前路哦。
当然,如果你是一名数据分析员,POWERBI最好也是要学的。
原因很简单,它很简单。
5.说了这么多,那么,如何在Excel中使用SQL?
一般有三种方法。
一种是MSQuery法,不常用,省略。
一种是OLEDB法,具体过程是,单击Excel【数据】选项卡下的【现有链接】,在弹出的【现有链接】对话框中,单击【浏览更多】,选取目标文件后,依次【确定】,得到下面的【导入数据】对话框。
这种方法通常搭配数据透视表(上图显示方式选择【数据透视表(P)】),也可以搭配PowerPivot(高级版本Excel勾选上图的【将此数据添加到数据模型】)。
单击【属性】按钮后,得到【链接属性】对话框,再单击【定义(D)】选项卡,即可在【命令文本】编辑框中输入SQL语句,并【确定】执行。
关于上图【连接字符串】中的关键字和关联值,我们会在以后的AOD部分详加说明,此处先过。
最后一种是VBA+ADO法,也是我们后文中常使用的方法。
相比于第2种方法,VBA+ADO法的优点……
首先是自动化,它可以使用VBA代码绑定ADO,设定链接字符串,执行SQL语句,进而一键获取分析数据。
其次,VBA编程可以使用变量编辑SQL语句,这远比第2种方法手动输入SQL语句要灵活智能的多,另外,VBA+ADO法不但可以SELECT(查询)数据,还可以INSERT(增)DELETE(删)UPDATE(改)数据库的数据等。
ExcelVBA+ADO+SQL入门教程002:
简单认识ADO
ADO(ActiveXDataObjects,ActiveX数据对象)是微软提出的应用程序接口,用以实现访问关系或非关系数据库中的数据……更多概念信息请自行咨询XX君,无赖脸。
之所以要学习ADO,一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的;而首要原因是,在EXCELVBA中,一般只有通过ADO,才可以使用强大的SQL查询语言访问外部数据源,进而查、改、增、删外部数据源中的数据。
后面这话延伸在具体编程操作上,就形成了四步走发展战略……
1.VBA引用ADO类库。
2.ADO建立对数据源的链接。
3.ADO执行SQL语言。
4.VBA处理SQL查询结果。
嗯,这就好比你先找个女(男)朋友,然后谈恋爱,最后结婚……
2.在VBA中引用ADO类库一般有两种方式。
一种是前期绑定。
所谓前期绑定,是指在VBE中手工勾选引用MicrosoftADO相关类库。
在Excel中,按
在【可使用的引用】列表框中,勾选“MicrosoftActiveXDataObjects2.8Library”库,或“MicrosoftActiveXDataObjects6.1Library”库,单击【确定】按钮关闭对话框。
另一种是使用代码后期绑定。
Sub后期绑定()
DimcnnAsObject
Setcnn=CreateObject("adodb.connection")
EndSub
两种方式的主要区别是,前期绑定后,在代码编辑过程中,VBE的“自动列出成员”功能,可以提供ADO的属性和方法,这便于代码快捷、准确的编写,但当他人的Excel工作簿并没有手工前期绑定ADO类库时,相关代码将无法运行;因此后期代码绑定ADO的通用性会更强些,它不需要手工绑定相关类库。
星光俺老油……老江湖的经验是,代码编写及调试时,使用前期绑定,代码完善后,再修改为后期绑定发布使用。
3.不论我们使用SQL语言对数据源作何操作,都得首先使用ADO创建并打开一个由VBA到数据源的链接;这就好比得先修路,才能使用汽车运输货物。
在VBA中,我们通常使用ADO的Connection.Open语句来显式建立一个到数据源的链接。
Connection.Open语法如下:
connection.OpenConnectionString,UserID,Password,Options
ConnectionString可选,字符串,包含连接信息。
UserID可选,字符串,包含建立连接时所使用用户名。
Password可选,字符串,包含建立连接时所使用密码。
Options可选,决定该方法是在连接建立之后(异步)还是连接建立之前(同步)返回,默认是同步,adAsyncConnect是异步。
……语法看起来似乎很复杂?
不必烦扰,现在,对我们而言,重点只是大体了解一下参数ConnectionString,也就是连接字符串。
虽然不同的数据库或文件有不同的连接字符串,但常用的数据库或文件的连接字符串均是固定的。
举个例子,如果将代码所在的Excel(2016版)作为一个外部数据源建立链接,代码如下:
SubMycnn()
DimcnnAsObject
'定义变量
Setcnn=CreateObject("adodb.connection")
'后期绑定ADO
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;ExtendedProperties='Excel12.0;HDR=yes;IMEX=0';DataSource="&ThisWorkbook.FullName
'建立链接
cnn.Close
'关闭链接
Setcnn=Nothing
'释放内存
EndSub
说一下上面代码连接字符串中各关键字(字体加粗部分)的意思。
Provider是Connection对象提供者名称的字符串值,03版Excel是“Microsoft.jet.OLEDB.4.0”,其它版本可以使用“Microsoft.ACE.OLEDB.12.0”;
ExtendedProperties是Excel版本号及其它相关信息,03版本是Excel8.0,其它版本可以使用Excel12.0。
其中HDR项是引用工作表是否有标题行,默认值HDR=Yes,意思是引用表的第一行是标题行,标题只能一行,不能多行,亦不能存在合并单元格。
HDR=no,意思是引用表不存在标题行,也就是说第一行开始就是数据记录了;此时,相关字段名在SQL语句中可以使用f加序列号表示,第1列字段名是f1,第2列字段名是f2,其余以此类推,f是英文field(字段)的缩写。
IMEX项是汇入模式,默认为0(只读模式),1是只写,2是可读写。
当参数设置为1时,除了只写,还有默认全部记录数据类型为文本的用途,关于这一点及其限制前提我们以后再谈。
DataSource是数据来源工作薄的完整路径。
VBA代码Application.Version可以获取计算机的Excel版本号,因此以下代码兼顾了03及各高级版本Excel的情况:
SubMycnn2()
DimcnnAsObject
DimMypathAsString
DimStr_cnnAsString
Setcnn=CreateObject("adodb.connection")
Mypath=ThisWorkbook.FullName
'数据源工作簿的完整路径
IfApplication.Version<12Then
'判断Excel版本号,以使用不同的连接字符串
Str_cnn="Provider=Microsoft.jet.OLEDB.4.0;ExtendedProperties=Excel8.0;DataSource="&Mypath
Else
Str_cnn="Provider=Microsoft.ACE.OLEDB.12.0;ExtendedProperties=Excel12.0;DataSource="&Mypath
EndIf
cnn.OpenStr_cnn
cnn.Close
Setcnn=Nothing
EndSub
最后,需要提醒大家的是,链接是一种昂贵的资源(官方语),因此在代码运行完毕后,请养成关闭链接(cnn.Close)并释放内存(Setcnn=Nothing)的好习惯。
本节小贴士:
3.1,连接字符串中各关键字的对应值可能和大小写有关,这是因为不同数据库的要求可能不一样,但通常来说,关键字和大小写无关,例如Provider,可以写成provider或者PROVIDER。
不过,虽然关键字和大小写无关,但和拼写正确与否……当然是有关的!
(想啥呢哥们?
)当手打的连接字符串代码运行出错时,建议先复制正确的运行,再仔细核对个人错漏之处。
3.2,连接字符串中各关键字之间使用英文分号(;)间隔,例如(关键字1=值1;关键字2=值2;关键字3=值3……),另外,任何包含分号、单引号或双引号的值必须用双引号引起来,由于在VBA中连接字符串的外层已经存在了一个双引号,因此通常使用英文单引号进行转义,例如上例中的ExtendedProperties='Excel12.0;HDR=yes;IMEX=2',抄写时,千万别漏了英文单引号哦。
3.3,星光俺掐指一算,算出相当一部分童鞋英语水平堪忧,想来拼写这段英文连接字符串错漏百出是很有可能的,因此特呈上锦囊一份,参见下图。
别问我这图是哪来的,如果不几道,佛山无银脚,出门右拐重看第一章吧~
如果这锦囊您也不想用——其实收藏本帖,用到时打开帖子复制粘贴相关代码就可以了——嘿嘿,木错,这才是最常用的一招。
4.聊完了如何绑定ADO以及建立与数据源的链接……
最后说下如何使用ADO执行SQL语句。
别走开,喝杯水先我,咕咚咕咚……我们可以使用ADO的Connection对象或Recordset、Commannd执行SQL语句;详细内容我们放到ADO部分再讲;这里大家只需要先了解Connection对象的Execute方法就可以了。
这是一个最常用的VBA+ADO+SQL套路化查询代码,通常,我们只需要修改SQL语言以及放置查询结果的单元格位置。
SubDoSql_Execute1()
DimcnnAsObject,rstAsObject
DimMypathAsString,Str_cnnAsString,SqlAsString
DimiAsLong
Setcnn=CreateObject("adodb.connection")
'以上是第一步,后期绑定ADO
Mypath=ThisWorkbook.FullName
IfApplication.Version<12Then
Str_cnn="Provider=Microsoft.jet.OLEDB.4.0;ExtendedProperties=Excel8.0;DataSource="&Mypath
Else
Str_cnn="Provider=Microsoft.ACE.OLEDB.12.0;ExtendedProperties=Excel12.0;DataSource="&Mypath
EndIf
cnn.OpenStr_cnn
'以上是第二步,建立链接
Sql="SELECT姓名,成绩FROM[Sheet1$]WHERE成绩>=80"
'Sql语句,查询Sheet1表成绩大于80……姓名和成绩的记录
Setrst=cnn.Execute(Sql)
'cnn.Execute()执行SQL语句,始终得到一个新的记录集rst
'以上是第三步,编写并使用SQL语句
[d:
e].ClearContents
'清空[d:
e]区域的值
Fori=0Torst.Fields.Count-1
'利用fields属性获取所有字段名,fields包含了当前记录有关的所有字段,fields.count得到字段的数量
'由于Fields.Count下标为0,又从0开始遍历,因此总数-1
Cells(1,i+4)=rst.Fields(i).Name
Next
Range("d2").CopyFromRecordsetrst
'使用单元格对象的CopyFromRecordset方法将rst内容复制到D2单元格为左上角的单元格区域
'以上是第四步,将SQL查询结果和字段名写入表格指定区域
cnn.Close
'关闭链接
Setcnn=Nothing
'释放内存
EndSub
ExcelVBA+ADO+SQL入门教程003:
字段的查询
还是需要对一些名词做出解释;这些名词是SQL语句释义中经常使用到的,所以请先有一个大概的了解。
1.1数据库
数据库是按照数据结构来组织、储存和管理数据的仓库,例如最基础的ACCESS软件等。
通常情况下,我们将一个Excel工作簿视为一个数据库。
是的,如您所知,Excel并不是数据库,但这并不妨碍我们把它看成一个数据库,进而使用SQL语言的某些功能处理数据。
这就好比尽管男人不是女人,但一样可以织毛衣、做月嫂……不过,得承认,生孩子之类的就确实太富有挑战性了。
1.2数据表
数据表,或称之为表,是数据库最重要的组成部分之一,一个数据库由一个或一组数据表组成。
如果我们把这句话的“数据库”,替换成“Excel工作簿”,就变成这样:
表是Excel工作簿最重要的组成部分之一,一个Excel工作簿由一个或一组表组成,你看,同样也是成立的——因此,可以先将Excel工作表视为数据库中的“表”,至于异同,以后再聊。
1.3记录和字段
如上图所示,和Excel表格一样,数据库的表由行和列组成,只是不存在行号和列标,相关值自然就不能使用A6、F4之类的Excel单元格地址来表述。
表的每一行描述实体的一个实例,称之为记录;每一列描述实体的一个特征或属性,称之为字段,列的标题称之为字段名或列标题,例如上图中的“姓名”。
主键和外键……以后用到再说。
没了。
2.SQL的数据查询和操纵语句包括SELECT、UPDATE、INSERT、DELETE等,也就是所谓的查改增删,其中最重要、使用最频繁的是SELECT查询语句。
查询按照复杂性划分,可以从一个简单的语句返回所有表中所有的记录,到用一个语句链接多个表并定义多种搜索条件……
现在,就让我们从最简单的部分走起吧——走你,少年。
SELECT语法如下(简化版):
SELECT字段名FROM表名
SELECT关键字指明了要查询的字段名称,FROM关键字指明了要获取字段信息的表的名称。
倘若数据源是Excel表格,需要在表名后增加美元符号$,并用中括号包起来,例如[Sheet1$]。
另外需要说明的是,SQL语言对关键字大小写不敏感。
SELECT、select、Select都可以使用,作用等同;基于书写规范化的原因,可能有些老师会要求关键字统一大写;嗯,我不是老师,我还小;我的意见是新手期怎么喜欢怎么顺眼怎么来——你好就好,宠溺脸。
3.分享一下SQLInExcel常用字段查询语句以及常见问题的解决方法。
如下图所示,是一份Excel表格,表名为学生表,内容是一些看见星光之类小学生的信息数据。
3.1,单个字段查询
假设我们需要查询上图所示表格的姓名列数据。
语句如下:
SELECT姓名FROM[学生表$]
结果如下:
3.2,多个字段查询
假设我们需要查询字段为姓名/性别/爱好的记录,语句如下:
SELECT姓名,性别,爱好FROM[学生表$]
不同字段名之间使用英文逗号间隔,但最后一个字段名不用。
另外需要格外注意的是,英文逗号千万别写成中文逗号,当然,初学阶段,别说千万,亿万万也没办法阻止咱们错输中文逗号的情况发生……反复切换输入法的中英文状态真的是很烦的一匹……。
上述语句查询结果如下:
3.3,查询所有字段的快捷方式
如果我们需要提取所有字段的数据呢?
一种方法是将所有字段名写入SELECT语句中:
SELECT ID,姓名,性别,年龄,爱好,得分FROM[学生表$]
另外一种方法是,使用星号(*)选取所有列:
SELECT*FROM[学生表$]
两者的区别在于,前者只选取指定字段的数据,后者包括了指定表的所有数据。
对于Excel表格而言,如果字段名确定,出于安全考虑,通常使用前者更靠谱点儿。
3.4,别名的使用
如果我们需要更改查询结果中的字段名称,例如我不喜欢字段名“爱好”,我想把它改成“特长”,可以使用关键字as,as是英文alias的缩写,也就是“别名”的意思。
语句如下:
SELECT姓名,爱好AS特长FROM[学生表$]
3.5,特殊字段名的处理
当字段名存在某些特殊字符,例如空格,或者当字段名存在SQL语言的保留字,例如AS,该字段名需要使用中括号括起来,避免因语法识别混乱,系统产生错误信息的提示。
举个例子,如上图所示,Excel表名为调查表,该表字段名中有的存在空格(姓名),有的使用了SQL的保留字as,如果需要查询该表格姓名和地址的信息,SQL语句如下:
select[姓名],[as] from[调查表$]
查询结果如下:
3.6,无字段名情况的处理
之前讲过,Excel并不是数据库,对数据结构并没有强制性要求,因为这样的缘故,在使用SQL处理数据时,难免会碰上各种有趣的现象。
例如
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel VBA+ADO+SQL VBA ADO SQL