Excel_VBA＋ADO＋SQL入门教程_－_汇总.pdf

发布时间：2022-06-06 发布人：admin 分类：说明书资料大小：4.31M 资料格式：pdf 举报版权申诉

weixin_44172946-12250745-4744300845226074715.pdf-第1页.png

第1页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第2页.png

第2页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第3页.png

第3页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第4页.png

第4页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第5页.png

第5页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第6页.png

第6页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第7页.png

第7页 / 共111页

weixin_44172946-12250745-4744300845226074715.pdf-第8页.png

第8页 / 共111页

文本预览

Excel VBA+ADO+SQL 入门教程一、认识 SQL In Excel ............................................................................................................ 1 二、如何在 VBA 中执行 SQL 语句 .......................................................................................... 6 三、字段查询 ........................................................................................................................ 15 四、 SQL 中的 Excel 的表 ...................................................................................................... 20 五、 SQL 之字段去重 ............................................................................................................. 25 六、排序和名次 ..................................................................................................................... 27 七、条件查询（上） .............................................................................................................. 31 八、条件查询（下） .............................................................................................................. 34 九、聚合统计 ........................................................................................................................ 39 十、分组聚合 ........................................................................................................................ 43 十一、 SELECT 语法结构和运算顺序 .................................................................................... 48 十二、 UNION 多表数据合并 ................................................................................................. 50 十三、子查询 ........................................................................................................................ 56 十四、多表连接查询（上） ................................................................................................... 62 十五、多表连接查询（中） ................................................................................................... 65 十六、多表连接查询（下） ................................................................................................... 68 十七、交叉表查询 ................................................................................................................. 72 十八、 DELETE 删除语句 ...................................................................................................... 78 十九、 UPDATE 更新 ............................................................................................................. 82 二十、 INSERT 新增数据 ....................................................................................................... 86 二十一、 ADO 常用连接字符串 .............................................................................................. 91 二十二、 Connection 对象的 Execute 方法 ............................................................................ 94 二十三、 OpenSchema 获取表信息 ....................................................................................... 96 二十四、初识 Recordset 对象 ............................................................................................. 103 二十五、将记录集转换为数组 ............................................................................................. 103 一、认识 SQL In Excel Excel VBA+ADO+SQL 入门教程 001：认识 SQL In Excel Excel 之家 ExcelHome 2018-02-01 1.本文所有观点及论述均是基于 Excel 平台，更准确的说是 MS Excel……如无特殊情况，文中将不再特殊说明。 2.以星光俺行走江湖多年的经验来看，学习者可以分为两类，一类是被动的学习者，完全或者少有清醒的自我思考意识，书上写什么，我就看什么，老师讲什么，我就听什么，啊，世界如此单纯我亦无忧无虑不要长大不要……。还有一类是主动的学习者，简而言之，TA 知道自己学的是什么，为什么而学，怎么样才能学的透彻…… 譬如说罢，咱们今天和以后分享的 SQL、ADO 等，后者就会问，这俩货是什么？为什么要学呀？学了有什么用啊？难不难学——和谈恋爱比起来？ 3.那就先说 SQL 吧。第 1 页共 111 页

SQL 是一种结构化查询语言（Structured Query Language），是一种声明式语言，敲黑板划重点【结构化和声明式】。SQL 的核心是对表的引用，声明你想从数据源中获取什么样的结果，而不用告诉计算机如何才能够得到结果—— 后面这句话似乎很难理解，举例来说，倘若我们需要获取上图所示表格（Sheet1）成绩大于等于 80 分的人员名单，如果用命令式程序语言，比如 VBA，是这样的： Sub MyFind() Dim arr, brr, i&, k& arr = Sheet1.[a1].CurrentRegion ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2)) For i = 1 To UBound(arr) If arr(i, 2) >= 80 Then k = k + 1 brr(k, 1) = arr(i, 1) brr(k, 2) = arr(i, 2) End If Next [d:f].ClearContents [d1].Resize(k, 2) = brr End Sub 你需要通过 VBA 编程告诉计算机每一步怎么走，数据从哪里来，从哪里开始遍历，行列是多少，符合条件的数据装入哪里，怎么装等等…… 而如果用声明式 SQL 语言呢？只要告诉计算机我要什么就可以了。 SELECT 姓名,成绩 FROM [Sheet1$] WHERE 成绩>=80 第 2 页共 111 页

我要 Sheet1 表（FROM [Sheet1$]）……成绩大于等于 80（WHERE 成绩>=80）……姓名和成绩的数据（SELECT 姓名,成绩）。只要结果，不问过程。就酱紫的声明式霸道总裁范。 4.为什么要学习 SQL In Excel（Excel 支持的 SQL 语言）呢？换言之，相比于 Excel 其它功能，例如函数、VBA、POWER PIVOT 等，SQL 有何优势？首先，必须严肃脸说明的是，对于普通 Excel 使用者而言，VBA、SQL 以及以后提及的 ADO 并不是非学不可的，非学不可的是基础操作、函数、透视表、图表…… 然而大数据时代，对于另外相当一部分表族而言，Excel 用久了，慢慢的，会意识到一个大问题；曾经在你心中无比强大的 Excel 函数，原来只适合小数据的腾挪躲闪；当数据量稍大后，函数这货就像未嗑士力架的姚明——不来劲的很哩。 SQL In Excel 则可以解决函数处理大数据效率低下的问题，嗯~使用 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，毕竟数组遍历在手，天下我有，什么合并单元格多行表头，统统都是浮云…… 最后，SQL In Excel 和 Power Pivot For Excel 相比优势在哪里？如果只谈数据处理部分，SQL In Excel 最大的优势……当然是 VBA 了。VBA 通过 ADO 执行 SQL 语言，可以获取、分析多种来源的数据，甚至进而对获取的数据再搭配字典、数组以及各种 Excel 自带的功能作进一步自动化、智能化处理……。嗯，POWER PIVOT 并不支持 VBA，耸肩，无奈。 5.说了这么多，那么，如何在 Excel 中使用 SQL？一般有三种方法。一种是 MS Query 法，不常用，省略。一种是 OLE DB 法，具体过程是，单击 Excel【数据】选项卡下的【现有链接】，第 3 页共 111 页

在弹出的【现有链接】对话框中，单击【浏览更多】，选取目标文件后，依次【确定】，得到下面的【导入数据】对话框。这种方法通常搭配数据透视表（上图显示方式选择【数据透视表(P)】），也可以搭配 Power Pivot（高级版本 Excel 勾选上图的【将此数据添加到数据模型】）。单击【属性】按钮后，得到【链接属性】对话框，再单击【定义(D)】选项卡，即可在【命令文本】编辑框中输入 SQL 语句，并【确定】执行。第 4 页共 111 页

关于上图【连接字符串】中的关键字和关联值，我们会在以后的 AOD 部分详加说明，此处先过。最后一种是 VBA+ADO 法，也是我们后文中常使用的方法。相比于第 2 种方法，VBA+ADO 法的优点…… 首先是自动化，它可以使用 VBA 代码绑定 ADO，设定链接字符串，执行 SQL 语句，进而一键获取分析数据。其次，VBA 编程可以使用变量编辑 SQL 语句，这远比第 2 种方法手动输入 SQL 语句要灵活智能的多，另外，VBA+ADO 法不但可以 SELECT（查询）数据，还可以 INSERT（增）DELETE（删）UPDATE（改）数据库的数据等。 6.……握握爪，今天我们就先聊到这里吧，下期我们简单聊下 ADO，然后聊 SQL 语言中最常用的 SELECT 语句…… 第 5 页共 111 页

嗯，忘记回答一个很重要的问题。 SQL 难吗？入门很容易，精通很难。对于 EXCELer，并没有精通的必要性，搭配 ADO 以及 VBA 自身的功能，例如数组和字典，入门 SQL 已经足够了。你要对……我有信心（忐忑脸）。安，爱你们，下期见。二、如何在 VBA 中执行 SQL 语句本文所有论述和观点均是基于 Excel 平台，更准确的说是 MS Excel……如无特殊情况，文中将不再作特殊说明。 1.诸君好。上期我们认识了 SQL：Excel VBA+ADO+SQL 入门教程 001：认识 SQL In Excel 这期我们聊下 ADO。 ADO 是什么？为什么要学 ADO？ ADO (ActiveX Data Objects，ActiveX 数据对象）是微软提出的应用程序接口，用以实现访问关系或非关系数据库中的数据……更多概念信息请自行咨询百度君，无赖脸。之所以要学习 ADO，一个原因是 ADO 自身的一些属性和方法对于数据处理是极其有益的；而首要原因是，在 EXCEL VBA 中，一般只有通过 ADO，才可以使用强大的 SQL 查询语言访问外部数据源，进而查、改、增、删外部数据源中的数据。后面这话延伸在具体编程操作上，就形成了三步走发展战略（鼓掌）…… 1.引用 ADO 类库。 2.ADO 建立对数据源的链接。 3.ADO 执行 SQL 语言。嗯，这就好比你先找个女（男）朋友，然后谈恋爱，最后结婚…… 2. 在 VBA 中引用 ADO 类库一般有两种方式。一种是前期绑定。所谓前期绑定，是指在 VBE 中手工勾选引用 Microsoft ADO 相关类库。在 Excel 中，按快捷键打开 VBA 编辑窗口，依次单击【工具】→【引用】，打开【引用-VBAProject】对话框。在【可使用的引用】列表框中，勾选“Microsoft ActiveX Data Objects 2.8 Library”库，或“Microsoft ActiveX Data Objects 6.1 Library”库，单击【确定】按钮关闭对话框。第 6 页共 111 页

一种是使用代码后期绑定。 Sub 后期绑定() Dim cnn As Object Set cnn = CreateObject("adodb.connection") End Sub 两种方式的主要区别是，前期绑定后，在代码编辑过程中，VBE 的“自动列出成员”功能，可以提供 ADO 的属性和方法，这便于代码快捷、准确的编写，但当他人的 Excel 工作簿并没有手工前期绑定 ADO 类库时，相关代码将无法运行；因此后期代码绑定 ADO 的通用性会更强些，它不需要手工绑定相关类库。星光俺老油……老江湖的经验是，代码编写及调试时，使用前期绑定，代码完善后，再修改为后期绑定发布使用。 3. 不论我们使用 SQL 语言对数据源作何操作，都得首先使用 ADO 创建并打开一个由 VBA 到数据源的链接；这就好比得先修路，才能使用汽车运输货物。在 VBA 中，我们通常使用 ADO 的 Connection.Open 语句来显式建立一个到数据源的链接。 Connection.Open 语法如下： Connection.Open ConnectionString, UserID, Password, Options ConnectionString 可选，字符串，包含连接信息。 UserID 可选，字符串，包含建立连接时所使用用户名。 Password 可选，字符串，包含建立连接时所使用密码。 Options 可选，决定该方法是在连接建立之后（异步）还是连接建立之前（同步）第 7 页共 111 页

返回，默认是同步，adAsyncConnect 是异步。 ……语法看起来似乎很复杂？不必烦扰，现在，对我们而言，重点只是参数 ConnectionString，也就是连接字符串。虽然不同的数据库或文件有不同的连接字符串，但常用的数据库或文件的连接字符串均是固定的。举个例子，如果将代码所在的 Excel（2016 版）作为一个外部数据源建立链接，代码如下： Sub Mycnn() Dim cnn As Object '定义变量 Set cnn = CreateObject("adodb.connection") '后期绑定 ADO cnn.Open"Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties='Excel 12.0;HDR=yes;IMEX=0';Data Source=" & ThisWorkbook.FullName '建立链接 cnn.Close '关闭链接 Set cnn = Nothing '释放内存 End Sub 说一下上面代码连接字符串中各关键字（字体加粗部分）的意思。 Provider 是 Connection 对象提供者名称的字符串值， 03 版 Excel 是 “Microsoft.jet.OLEDB.4.0”，其它版本可以使用“Microsoft.ACE.OLEDB.12.0”； Extended Properties 是 Excel 版本号及其它相关信息，03 版本是 Excel 8.0，其它版本可以使用 Excel 12.0。其中 HDR 项是引用工作表是否有标题行，默认值 HDR=Yes，代表引用表的第一行是标题行，标题只能一行，不能多行，亦不能存在合并单元格。HDR=no，代表引用表不存在标题行，也就是说第一行开始就是数据记录了；此时，相关字段名在 SQL 语句中可以使用 f 加序列号表示，第 1 列字段名是 f1，第 2 列字段名是 f2，其余以此类推。 IMEX 项是汇入模式，默认为 0（只读模式），1 是只写，2 是可读写。当参数设置为 1 时，除了只写，还有默认全部记录数据类型为文本的用途，关于这一点及其限制前提我们以后再谈。 Data Source 是数据来源工作薄的完整路径。 VBA 代码 Application.Version 可以获取计算机的 Excel 版本号，因此以下代码兼顾了 03 及各高级版本 Excel 的情况： Sub Mycnn2() Dim cnn As Object 第 8 页共 111 页

分享到：

赞收藏

资料库

Excel_VBA＋ADO＋SQL入门教程_－_汇总.pdf

相关推荐

信息化

热门标签

最新资料