ExcelVBA+ADO+SQL.docx

ExcelVBA+ADO+SQL.docx

《ExcelVBA+ADO+SQL.docx》由会员分享，可在线阅读，更多相关《ExcelVBA+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中，按快捷键打开VBA编辑窗口，依次单击【工具】→【引用】，打开【引用-VBAProject】对话框。

在【可使用的引用】列表框中，勾选“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处理数据时，难免会碰上各种有趣的现象。

例如，