在Excel VBA中写SQL，是一种什么体验

　　每每提到Excel办公自动化，我们脑海里能想到的就是公式、数据透视表、宏、VBA，这也是我们大部分人数据分析的进阶之路。当我们对于常用VBA技巧已经相当熟练后，往往会有一种“我的VBA知识够用啦”的错觉，其实那只是因为我们收到的实际需求还不够复杂和多样化。

 

 

　　一旦哪天碰到略显复杂的业务需求时，我们才知道VBA变量、循环、条件判断、数组、字典、窗体，这些还只是VBA的基础知识罢了，会了这些，远达不到【蒂花之秀】的水准。我们今天的主角，SQL（结构化查询语言），通过嵌入VBA代码中，就可以解决很多复杂的实际问题。

　　考虑到写VBA的大军主要还是办公人员，如财务岗、行政岗，并非科班程序员出身，他们往往觉得SQL是后端程序员才使用的语言，并不那么容易掌握，其实这不过是另一种认知的偏差。我们今天就通过一个简单例子，看看在VBA中如何写SQL，是一种什么体验。

 

　　想要在VBA中使用SQL，我们就不得不先认识下ADO。

ADO,其实是ActiveX Data Objects的简称，说人话，它其实是几个ActiveX对象（类），用于提供数据(Data)之间的访问；

Connection：ADO Connection 对象用于创建一个访问某个数据源的开放连接。基于此开放连接，我们可以对一个数据库进行访问和相关操作；

Recrodset:ADO Recordset 对象用于存储一个来自数据库表的记录集。一个 Recordset 对象由记录和列（字段）组成。它也是我们最常用的操作数据库的对象。

　　我们数据库的数据源（dataSource）可以是Access、SQL Server、Oracle 等数据库，也可以是简单的text文件，甚至可以是一个excel文件。考虑到办公人员的主要数据来源都是excel文件，我们先来看看如何连接excel文件作为我们的数据源。

 

03版本Excel（.xls)

Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=’Excel 8.0;HDR=yes;IMEX=0’;Data Source=带路径的Excel工作簿完整名称；

 

07~19版本Excel(.xlsx 或.xlsm)

Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=’Excel 12.0; HDR=yes;IMEX=0’;Data Source=带路径的Excel工作簿完整名称。

 

如何理解【Data Source=带路径的Excel工作簿完整名称】这句话？

　　翻译成程序语言，就是要这个文件的完整路径，如果我们要控制的数据源就是我们当前的xlsm宏文件，那么就是Data Source=thisworkbook.FullName,如果是同目录下某个'test.xlsx'文件，我们则可以写作：Data Source=thisworkbook.path &"\test.xlsx"。

　　有了这些基础知识，我们还需要知道SQL的一些基础语法，此处推荐SQL 教程 (w3school.com.cn) 进行入门。

现在我们就可以使用SQL Select语句做一个最基础的查询了。

 

场景一：利用SQL将【表一】数据拷贝到【表二】

 

 代码如下：

Sub myFirstQuery()
    '将表一的数据查询到后，返回到表二中，包含字段名
    Dim CONN As Object, sht As Worksheet, RS As Object, i As Integer
    Set CONN = CreateObject("ADODB.Connection")
    Set sht = ThisWorkbook.Sheets("表二")
    CONN.Open "provider=Microsoft.Ace.oledb.12.0;Extended Properties=Excel 12.0;data source=" & ThisWorkbook.FullName
    Sql = "SELECT *  FROM [表一$]"  '查找表一的所有数据，*默认指查找所有字段内容
    Set RS = CONN.Execute(Sql)
    
    For i = 0 To RS.Fields.Count - 1 '输出字段名，也就是excel列名，索引从0开始，而Excel行列索引号均从1开始计数
        sht.Cells(1, i + 1).Value = RS.Fields(i).Name
    Next
    sht.Cells(2, 1).CopyFromRecordset RS '将查询到的recordset数据集粘贴到表二的A２单元格
    CONN.Close
End Sub

 

场景二：筛选【表一】中 姓名为‘温宁’的数据到【表二】中

其实，有了sql基础，我们只需要对上面的SQL代码加上Where条件判断子句即可。

 

代码如下：

Sub myFirstQuery()
    '将表一的数据查询到后，返回到表二中，包含字段名
    Dim CONN As Object, sht As Worksheet, RS As Object, i As Integer
    Set CONN = CreateObject("ADODB.Connection")
    Set sht = ThisWorkbook.Sheets("表二")
    CONN.Open "provider=Microsoft.Ace.oledb.12.0;Extended Properties=Excel 12.0;data source=" & ThisWorkbook.FullName
    Sql = "SELECT *  FROM [表一$] WHERE 姓名='温宁'"  '查找表一中姓名='温宁'的所有数据
    Set RS = CONN.Execute(Sql)
    
    For i = 0 To RS.Fields.Count - 1 '输出字段名，索引从0开始，而Excel行列索引号均从1开始计数
        sht.Cells(1, i + 1).Value = RS.Fields(i).Name
    Next
    sht.Cells(2, 1).CopyFromRecordset RS '将查询到的recordset数据集粘贴到表二的A２单元格
    CONN.Close
End Sub

 

　　通过以上两个场景不难看出，核心的逻辑只需要一句SQL就足以，SQL并不是什么程序员才能掌握的东西，我们完全不用担心无法掌握它。不过我们每次使用前都要提前定义CONNECTION、RECORDSET等对象，包括输出字段名等必要操作。因此，当如果我们的业务足够简单时，使用VBA中的基础功能反而写起来更快，当业务逻辑复杂时，我们才会重点考虑SQL，永远不用为了用SQL而刻意写SQL。用对了地方，SQL就是六脉神剑；用错了地方，就真成了花里胡哨的炫技，Duck不必。

欢迎扫码关注我的公众号 获取更多爬虫、数据分析的知识！