Excel VBA+ADO+SQL入門教程002：簡單認識ADO

本文所有論述和觀點均是基於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”庫，單擊【確定】按鈕關閉對話框。

一種是使用代碼後期綁定。

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可選，決定該方法是在連接建立之後（異步）還是連接建立之前（同步）返回，默認是同步，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

其中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

Dim Mypath As String

Dim Str_cnn As String

Set cnn = CreateObject("adodb.connection")

Mypath = ThisWorkbook.FullName

'數據源工作簿的完整路徑

If Application.Version < 12 Then

'判斷Excel版本號，以使用不同的連接字符串

Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath

Else

Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath

End If

cnn.Open Str_cnn

cnn.Close

Set cnn = Nothing

End Sub

最後，需要提醒大家的是，鏈接是一種昂貴的資源（官方語），因此在代碼運行完畢後，請養成關閉鏈接（cnn.Close）並釋放內存（Set cnn = Nothing）的好習慣。

本節小貼士：

3.1，

連接字符串中各關鍵字的對應值可能和大小寫有關，這是因為不同數據庫的要求可能不一樣，但通常來說，關鍵字和大小寫無關，例如Provider，可以寫成provider或者PROVIDER。不過，雖然關鍵字和大小寫無關，但和拼寫正確與否……當然是有關的！（想啥呢哥們？）。當手打的連接字符串代碼運行出錯時，建議先複製正確的運行，再仔細核對個人錯漏之處。

3.2，

連接字符串中各關鍵字之間使用英文分號（;）間隔，例如（關鍵字1=值1;關鍵字2=值2;關鍵字3=值3……），另外，任何包含分號、單引號或雙引號的值必須用雙引號引起來，由於在VBA中連接字符串的外層已經存在了一個雙引號，因此通常使用英文單引號進行轉義，例如上例中的Extended Properties='Excel 12.0;HDR=yes;IMEX=2'，抄寫時，千萬別漏了英文單引號哦。

3.3，

星光俺掐指一算，算出相當一部分童鞋英語水平堪憂，想來拼寫這段英文連接字符串錯漏百出是很有可能的，因此特呈上錦囊一份，參見下圖。別問我這圖是哪來的，如果不幾道，佛山無銀腳，出門右拐重看第一章吧~

4.

聊完了如何綁定ADO以及建立與數據源的鏈接……

最後說下如何使用ADO執行SQL語句。

別走開，喝杯水先我，咕咚咕咚……

啪，扔杯子，哥抬手瀟灑抹去嘴角的水漬，咳，咱們繼續吧……

——想不到還有這種操作吧？微微一笑~

通常我們使用ADO的Execute語句或Recordset對象執行SQL語句，有以下幾種方法。

Execute方法（一）：

Sub DoSql_Execute1()

Dim cnn As Object, rst As Object

Dim Mypath As String, Str_cnn As String, Sql As String

Dim i As Long

Set cnn = CreateObject("adodb.connection")

'以上是第一步，後期綁定ADO

'

Mypath = ThisWorkbook.FullName

If Application.Version < 12 Then

Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath

Else

Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath

End If

cnn.Open Str_cnn

'以上是第二步，建立鏈接

'

Sql = "SELECT 姓名,成績 FROM [Sheet1$] WHERE 成績>=80"

'Sql語句，查詢Sheet1表成績大於80……姓名和成績的記錄

Set rst = cnn.Execute(Sql)

'cnn.Execute()執行SQL語句，始終得到一個新的記錄集rst

'以上是第三步，編寫並使用SQL語句

'

[d:e].ClearContents

'清空[d:e]區域的值

For i = 0 To rst.Fields.Count - 1

'利用fields屬性獲取所有字段名，fields包含了當前記錄有關的所有字段,fields.count得到字段的數量

'由於Fields.Count下標為0，又從0開始遍歷，因此總數-1

Cells(1, i + 4) = rst.Fields(i).Name

Next

Range("d2").CopyFromRecordset rst

'使用單元格對象的CopyFromRecordset方法將rst內容複製到D2單元格為左上角的單元格區域

'以上是第四步，將SQL查詢結果和字段名寫入表格指定區域

'

cnn.Close

'關閉鏈接

Set cnn = Nothing

'釋放內存

End Sub

事實上，也可以不使用rst對象，只使用Execute語句，直接將SQL查詢的結果放入指定單元格區域。該方法的優點是代碼簡潔，缺點是隻獲得記錄，沒有字段名，或者說沒有標題欄，代碼如下。

Execute方法（二）：

Sub DoSql_Execute2()

Dim cnn As Object, rst As Object

Dim Mypath As String, Str_cnn As String, Sql As String

Dim i As Long

Set cnn = CreateObject("adodb.connection")

'以上是第一步，後期綁定ADO

'

Mypath = ThisWorkbook.FullName

If Application.Version < 12 Then

Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath

Else

Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath

End If

cnn.Open Str_cnn

'以上是第二步，建立鏈接

'

Sql = "SELECT 姓名,成績 FROM [Sheet1$] WHERE 成績>=80"

'Sql語句，查詢Sheet1表成績大於80……姓名和成績的記錄

[d:e].ClearContents

'清空[d:e]區域內容

Range("d2").CopyFromRecordsetcnn.Execute(Sql)

'Execute語句先執行SQL語句

'使用單元格對象的CopyFromRecordset方法將SQL查詢到的內容複製到D2單元格為左上角的單元格區域

'此法代碼簡潔，但未能獲取標題欄信息

'以上是第三步，執行SQL語句並將數據讀入表格指定區域

'

cnn.Close

'關閉鏈接

Set cnn = Nothing

'釋放內存

End Sub

除了使用Execute語句，也可以使用Recordset執行SQL。

Recordset方法：

Sub DoSql_Recordset()

Dim cnn As Object, rst As Object

Dim Mypath As String, Str_cnn As String, Sql As String

Dim i As Long

Set cnn = CreateObject("adodb.connection")

Set rst = CreateObject("adodb.Recordset")

'以上是第一步，後期綁定ADO，並創建rst記錄集

'

Mypath = ThisWorkbook.FullName

If Application.Version < 12 Then

Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath

Else

Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath

End If

cnn.Open Str_cnn

'以上是第二步，建立鏈接

'

Sql = "SELECT 姓名,成績 FROM [Sheet1$] WHERE 成績>=80"

'Sql語句，查詢Sheet1表成績大於80……姓名和成績的記錄

rst.Open Sql, cnn, 1, 3

'rst執行SQL語句，並將查詢結果放入記錄集

'以上是第三步，編寫並執行SQL

'

[d:e].ClearContents

'清空[d:e]區域的值

For i = 0 To rst.Fields.Count - 1

'利用fields屬性獲取所有字段名，fields包含了當前記錄有關的所有字段,fields.count得到字段的數量

'由於Fields.Count下標為0，又從0開始遍歷，因此總數-1

Cells(1, i + 4) = rst.Fields(i).Name

Next

Range("d2").CopyFromRecordset rst

'使用單元格對象的CopyFromRecordset方法將rst內容複製到D2單元格為左上角的單元格區域

'以上是第四步，將SQL查詢結果和字段名稱寫入表格指定區域

'

rst.Close

'關閉記錄集rst

cnn.Close

'關閉鏈接

Set rst = Nothing

Set cnn = Nothing

'釋放內存

End Sub

打個響指，關於第三個Recordset方法，目前知道有這事就好，至於它的語法，以及Recordset和Execute之間的區別，因為牽扯到更多專業的術語和知識，所以等我們聊完SQL常用語句後，詳聊ADO時再說。

本節內容我們只需要掌握使用Execute語句執行SQL，以及獲取字段名……也就是Execute方法（一）的代碼……就OK。

5.

各位親，以上內容看不懂的段落請直接跳過，不要糾結，不要給自己製造心理障礙，啊，這東西好難啊，我不會……看我眼睛，再重複一遍，看不懂的段落請直接跳過，這是一個好習慣。等整個系列進行了大概一半左右，再回頭來看，當初覺得困阻的地方原來也就那麼回事……

仰臉，擺擺手，經驗之談，都是經驗之談……心痛。

呵，總結一下：

對於新手而言，本章的重點是知道三步走發展戰略，以及懂得複製Execute方法（一）的代碼執行SQL語句，僅此而已，其它？擺擺我的小手，看過就算，大概瞭解下，留個印象，以後再見面好說話也就行了。

最後，還是留個練手題吧：

由於數據源並不存在標題行（HDR），且需要將匯入模式（IMEX）修改為讀寫，所以請將下面代碼中的鏈接字符串修改完善，並運行成功。

Sub Mycnn3()

Dim cnn As Object

Dim Mypath As String

Dim Str_cnn As String

Set cnn = CreateObject("adodb.connection")

Mypath = ThisWorkbook.FullName

If Application.Version < 12 Then

Str_cnn = "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & Mypath

Else

Str_cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & Mypath

End If

cnn.Open Str_cnn

cnn.Close

Set cnn = Nothing

End Sub

看完辛苦 轉發 關注 下哈，每天為你分享Excel技巧

安~擁抱~

閱讀更多 孫樹帥老師 的文章

關鍵字: 微軟 認識 數據庫