Excel-VBA+ADO+SQL

ExcelVBA+ADO+SQL入门教程001：认识SQLInExcel那就先说SQL吧。SQL是一种结构化查询语言（StructuredQueryLanguage），是一种声明式语言，敲黑板划重点【结构化和声明式】。SQL的核心是对表的引用，声明你想从数据源中获取什么样的结果，而不用告诉计算机如何才能够得到结果——后面这句话似乎很难理解，举例来说，倘若我们需要获取上图所示表格（Sheet1）成绩大于等于80分的人员名单，如果用命令式程序语言，比如VBA，是这样的：SubMyFind()Dimarr,brr,i&,k&arr=Sheet1.[a1].CurrentRegionReDimbrr(1ToUBound(arr),1ToUBound(arr,2))Fori=1ToUBound(arr)Ifarr(i,2)=80Thenk=k+1brr(k,1)=arr(i,1)brr(k,2)=arr(i,2)EndIfNext[d:f].ClearContents[d1].Resize(k,2)=brrEndSub你需要通过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：简单认识ADOADO(ActiveXDataObjects，ActiveX数据对象）是微软提出的应用程序接口，用以实现访问关系或非关系数据库中的数据……更多概念信息请自行咨询百度君，无赖脸。之所以要学习ADO，一个原因是ADO自身的一些属性和方法对于数据处理是极其有益的；而首要原因是，在EXCELVBA中，一般只有通过ADO，才可以使用强大的SQL查询语言访问外部数据源，进而查、改、增、删外部数据源中的数据。后面这话延伸在具体编程操作上，就形成了四步走发展战略……1.VBA引用ADO类库。2.ADO建立对数据源的链接。3.ADO执行SQL语言。4.VBA处理SQL查询结果。嗯，这就好比你先找个女（男）朋友，然后谈恋爱，最后结婚……2.在VBA中引用ADO类库一般有两种方式。一种是前期绑定。所谓前期绑定，是指在VBE中手工勾选引用MicrosoftADO相关类库。在Excel中，按Alt+F11快捷键打开VBA编辑窗口，依次单击【工具】→【引用】，打开【引用-VBAProject】对话框。在【可使用的引用】列表框中，勾选“MicrosoftActiveXDataObjects2.8Library”库，或“MicrosoftActiveXDataObjects6.1Library”库，单击【确定】按钮关闭对话框。另一种是使用代码后期绑定。Sub后期绑定()DimcnnAsObjectSetcnn=CreateObject(adodb.connection)EndSub两种方式的主要区别是，前期绑定后，在代码编辑过程中，VBE的“自动列出成员”功能，可以提供ADO的属性和方法，这便于代码快捷、准确的编写，但当他人的Excel工作簿并没有手工前期绑定ADO类库时，相关代码将无法运行；因此后期代码绑定ADO的通用性会更强些，它不需要手工绑定相关类库。星光俺老油……老江湖的经验是，代码编写及调试时，使用前期绑定，代码完善后，再修改为后期绑定发布使用。3.不论我们使用SQL语言对数据源作何操作，都得首先使用ADO创建并打开一个由VBA到数据源的链接；这就好比得先修路，才能使用汽车运输货物。在VBA中，我们通常使用ADO的Connection.Open语句来显式建立一个到数据源的链接。Connection.Open语法如下：connection.OpenConnectionString,UserID,Password,OptionsConnectionString可选，字符串，包含连接信息。UserID可选，字符串，包含建立连接时所使用用户名。Password可选，字符串，包含建立连接时所使用密码。Options可选，决定该方法是在连接建立之后（异步）还是连接建立之前（同步）返回，默认是同步，adAsyncConnect是异步。……语法看起来似乎很复杂？不必烦扰，现在，对我们而言，重点只是大体了解一下参数ConnectionString，也就是连接字符串。虽然不同的数据库或文件有不同的连接字符串，但常用的数据库或文件的连接字符串均是固定的。举个例子，如果将代码所在的Excel（2016版）作为一个外部数据源建立链接，代码如下：SubMycnn()DimcnnAsObject'定义变量Setcnn=CreateObject(adodb.connection)'后期绑定ADOcnn.OpenProvider=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()DimcnnAsObjectDimMypathAsStringDimStr_cnnAsStringSetcnn=CreateObject(adodb.connection)Mypath=ThisWorkbook.Ful