在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將【表一】數據拷貝到【表二】
程式碼如下：
1 Sub myFirstQuery() 2 '將表一的數據查詢到後，返回到表二中，包含欄位名 3 Dim CONN As Object, sht As Worksheet, RS As Object, i As Integer 4 Set CONN = CreateObject("ADODB.Connection") 5 Set sht = ThisWorkbook.Sheets("表二") 6 CONN.Open "provider=Microsoft.Ace.oledb.12.0;Extended Properties=Excel 12.0;data source=" & ThisWorkbook.FullName 7 Sql = "SELECT * FROM [表一$]" '查找表一的所有數據，*默認指查找所有欄位內容 8 Set RS = CONN.Execute(Sql) 9 10 For i = 0 To RS.Fields.Count - 1 '輸出欄位名，也就是excel列名，索引從0開始，而Excel行列索引號均從1開始計數 11 sht.Cells(1, i + 1).Value = RS.Fields(i).Name 12 Next 13 sht.Cells(2, 1).CopyFromRecordset RS '將查詢到的recordset數據集粘貼到表二的A２單元格 14 CONN.Close 15 End Sub
場景二：篩選【表一】中 姓名為『溫寧』的數據到【表二】中
其實，有了sql基礎，我們只需要對上面的程式碼加上Where條件判斷子句即可。
程式碼如下：
1 Sub myFirstQuery() 2 '將表一的數據查詢到後，返回到表二中，包含欄位名 3 Dim CONN As Object, sht As Worksheet, RS As Object, i As Integer 4 Set CONN = CreateObject("ADODB.Connection") 5 Set sht = ThisWorkbook.Sheets("表二") 6 CONN.Open "provider=Microsoft.Ace.oledb.12.0;Extended Properties=Excel 12.0;data source=" & ThisWorkbook.FullName 7 Sql = "SELECT * FROM [表一$] WHERE 姓名='溫寧'" '查找表一中姓名='溫寧'的所有數據 8 Set RS = CONN.Execute(Sql) 9 10 For i = 0 To RS.Fields.Count - 1 '輸出欄位名，索引從0開始，而Excel行列索引號均從1開始計數 11 sht.Cells(1, i + 1).Value = RS.Fields(i).Name 12 Next 13 sht.Cells(2, 1).CopyFromRecordset RS '將查詢到的recordset數據集粘貼到表二的A２單元格 14 CONN.Close 15 End Sub
通過以上兩個場景不難看出，核心的邏輯只需要一句SQL就足以，SQL並不是什麼程式設計師才能掌握的東西，我們完全不用擔心無法掌握它。不過我們每次使用前都要提前定義CONNECTION、RECORDSET等對象，包括輸出欄位名等必要操作。因此，當如果我們的業務足夠簡單時，使用VBA中的基礎功能反而寫起來更快，當業務邏輯複雜時，我們才會重點考慮SQL，永遠不用為了用SQL而刻意寫SQL。用對了地方，SQL就是六脈神劍；用錯了地方，就真成了花里胡哨的炫技，Duck不必。
歡迎掃碼關注我的公眾號 獲取更多爬蟲、數據分析的知識！
