在Excel VBA中寫SQL，是一種什麼體驗

NewJune發表於2022-01-23
ExcelSQL

　　每每提到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基礎，我們只需要對上面的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不必。

歡迎掃碼關注我的公眾號 獲取更多爬蟲、資料分析的知識！



相關文章