……忘了所有煩心 我把你緊緊擁入懷里 捧你在我手心 我大聲說(shuō)我愛(ài)的就是你……

本文所有論述和觀點(diǎn)均是基于Excel平臺(tái)，更準(zhǔn)確的說(shuō)是MS Excel……如無(wú)特殊情況，文中將不再作特殊說(shuō)明。

點(diǎn)擊文末【閱讀原文】可以下載示例文件。

1.

諸君好。

上期我們認(rèn)識(shí)了SQL：Excel VBA ADO SQL入門教程001：認(rèn)識(shí)SQL In Excel

這期我們聊下ADO。

ADO是什么？為什么要學(xué)ADO？

ADO (ActiveX Data Objects，ActiveX數(shù)據(jù)對(duì)象）是微軟提出的應(yīng)用程序接口，用以實(shí)現(xiàn)訪問(wèn)關(guān)系或非關(guān)系數(shù)據(jù)庫(kù)中的數(shù)據(jù)……更多概念信息請(qǐng)自行咨詢百度君，無(wú)賴臉。

之所以要學(xué)習(xí)ADO，一個(gè)原因是ADO自身的一些屬性和方法對(duì)于數(shù)據(jù)處理是極其有益的；而首要原因是，在EXCEL VBA中，一般只有通過(guò)ADO，才可以使用強(qiáng)大的SQL查詢語(yǔ)言訪問(wèn)外部數(shù)據(jù)源，進(jìn)而查、改、增、刪外部數(shù)據(jù)源中的數(shù)據(jù)。

后面這話延伸在具體編程操作上，就形成了三步走發(fā)展戰(zhàn)略（鼓掌）……

1.引用ADO類庫(kù)。

2.ADO建立對(duì)數(shù)據(jù)源的鏈接。

3.ADO執(zhí)行SQL語(yǔ)言。

嗯，這就好比你先找個(gè)女（男）朋友，然后談戀愛(ài)，最后結(jié)婚……（話筒：啊~有錢沒(méi)錢租個(gè)女友回家過(guò)年~

2.

在VBA中引用ADO類庫(kù)一般有兩種方式。

一種是前期綁定。

所謂前期綁定，是指在VBE中手工勾選引用Microsoft ADO相關(guān)類庫(kù)。

在Excel中，按<Alt F11>快捷鍵打開(kāi)VBA編輯窗口，依次單擊【工具】→【引用】，打開(kāi)【引用-VBAProject】對(duì)話框。在【可使用的引用】列表框中，勾選“Microsoft ActiveX Data Objects 2.8 Library”庫(kù)，或“Microsoft ActiveX Data Objects 6.1 Library”庫(kù)，單擊【確定】按鈕關(guān)閉對(duì)話框。

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

Sub 后期綁定()

Dim cnn As Object

Set cnn = CreateObject('adodb.connection')

End Sub

兩種方式的主要區(qū)別是，前期綁定后，在代碼編輯過(guò)程中，VBE的“自動(dòng)列出成員”功能，可以提供ADO的屬性和方法，這便于代碼快捷、準(zhǔn)確的編寫(xiě)，但當(dāng)他人的Excel工作簿并沒(méi)有手工前期綁定ADO類庫(kù)時(shí)，相關(guān)代碼將無(wú)法運(yùn)行；因此后期代碼綁定ADO的通用性會(huì)更強(qiáng)些，它不需要手工綁定相關(guān)類庫(kù)。

星光俺老油……老江湖的經(jīng)驗(yàn)是，代碼編寫(xiě)及調(diào)試時(shí)，使用前期綁定，代碼完善后，再修改為后期綁定發(fā)布使用。

3.

不論我們使用SQL語(yǔ)言對(duì)數(shù)據(jù)源作何操作，都得首先使用ADO創(chuàng)建并打開(kāi)一個(gè)由VBA到數(shù)據(jù)源的鏈接；這就好比得先修路，才能使用汽車運(yùn)輸貨物。

在VBA中，我們通常使用ADO的Connection.Open語(yǔ)句來(lái)顯式建立一個(gè)到數(shù)據(jù)源的鏈接。

Connection.Open語(yǔ)法如下：

connection.Open ConnectionString, UserID, Password, Options

ConnectionString可選，字符串，包含連接信息。

UserID可選，字符串，包含建立連接時(shí)所使用用戶名。

Password可選，字符串，包含建立連接時(shí)所使用密碼。

Options可選，決定該方法是在連接建立之后（異步）還是連接建立之前（同步）返回，默認(rèn)是同步，adAsyncConnect是異步。

……語(yǔ)法看起來(lái)似乎很復(fù)雜？不必?zé)_，現(xiàn)在，對(duì)我們而言，重點(diǎn)只是參數(shù)ConnectionString，也就是連接字符串。雖然不同的數(shù)據(jù)庫(kù)或文件有不同的連接字符串，但常用的數(shù)據(jù)庫(kù)或文件的連接字符串均是固定的。

舉個(gè)例子，如果將代碼所在的Excel（2016版）作為一個(gè)外部數(shù)據(jù)源建立鏈接，代碼如下：

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

    '關(guān)閉鏈接

    Set cnn = Nothing

    '釋放內(nèi)存

End Sub

說(shuō)一下上面代碼連接字符串中各關(guān)鍵字（字體加粗部分）的意思。

Provider是Connection 對(duì)象提供者名稱的字符串值，03版Excel是“Microsoft.jet.OLEDB.4.0”，其它版本可以使用“Microsoft.ACE.OLEDB.12.0”；

Extended Properties是Excel版本號(hào)及其它相關(guān)信息，03版本是Excel 8.0，其它版本可以使用Excel 12.0。

其中HDR項(xiàng)是引用工作表是否有標(biāo)題行，默認(rèn)值HDR=Yes，代表引用表的第一行是標(biāo)題行，標(biāo)題只能一行，不能多行，亦不能存在合并單元格。HDR=no，代表引用表不存在標(biāo)題行，也就是說(shuō)第一行開(kāi)始就是數(shù)據(jù)記錄了；此時(shí)，相關(guān)字段名在SQL語(yǔ)句中可以使用f加序列號(hào)表示，第1列字段名是f1，第2列字段名是f2，其余以此類推。

IMEX項(xiàng)是匯入模式，默認(rèn)為0（只讀模式），1是只寫(xiě)，2是可讀寫(xiě)。當(dāng)參數(shù)設(shè)置為1時(shí)，除了只寫(xiě)，還有默認(rèn)全部記錄數(shù)據(jù)類型為文本的用途，關(guān)于這一點(diǎn)及其限制前提我們以后再談。

Data Source是數(shù)據(jù)來(lái)源工作薄的完整路徑。

VBA代碼Application.Version可以獲取計(jì)算機(jī)的Excel版本號(hào)，因此以下代碼兼顧了03及各高級(jí)版本Excel的情況：

Sub Mycnn2()

    Dim cnn As Object

    Dim Mypath As String

    Dim Str_cnn As String

    Set cnn = CreateObject('adodb.connection')

    Mypath = ThisWorkbook.FullName

    '數(shù)據(jù)源工作簿的完整路徑

    If Application.Version < 12 Then

    '判斷Excel版本號(hào)，以使用不同的連接字符串

        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

最后，需要提醒大家的是，鏈接是一種昂貴的資源（官方語(yǔ)），因此在代碼運(yùn)行完畢后，請(qǐng)養(yǎng)成關(guān)閉鏈接（cnn.Close）并釋放內(nèi)存（Set cnn = Nothing）的好習(xí)慣。

本節(jié)小貼士：

3.1，

連接字符串中各關(guān)鍵字的對(duì)應(yīng)值可能和大小寫(xiě)有關(guān)，這是因?yàn)椴煌瑪?shù)據(jù)庫(kù)的要求可能不一樣，但通常來(lái)說(shuō)，關(guān)鍵字和大小寫(xiě)無(wú)關(guān)，例如Provider，可以寫(xiě)成provider或者PROVIDER。不過(guò)，雖然關(guān)鍵字和大小寫(xiě)無(wú)關(guān)，但和拼寫(xiě)正確與否……當(dāng)然是有關(guān)的?。ㄏ肷赌馗鐐?？）。當(dāng)手打的連接字符串代碼運(yùn)行出錯(cuò)時(shí)，建議先復(fù)制正確的運(yùn)行，再仔細(xì)核對(duì)個(gè)人錯(cuò)漏之處。

3.2，

連接字符串中各關(guān)鍵字之間使用英文分號(hào)（;）間隔，例如（關(guān)鍵字1=值1;關(guān)鍵字2=值2;關(guān)鍵字3=值3……），另外，任何包含分號(hào)、單引號(hào)或雙引號(hào)的值必須用雙引號(hào)引起來(lái)，由于在VBA中連接字符串的外層已經(jīng)存在了一個(gè)雙引號(hào)，因此通常使用英文單引號(hào)進(jìn)行轉(zhuǎn)義，例如上例中的Extended Properties='Excel 12.0;HDR=yes;IMEX=2'，抄寫(xiě)時(shí)，千萬(wàn)別漏了英文單引號(hào)哦。

3.3，

星光俺掐指一算，算出相當(dāng)一部分童鞋英語(yǔ)水平堪憂，想來(lái)拼寫(xiě)這段英文連接字符串錯(cuò)漏百出是很有可能的，因此特呈上錦囊一份，參見(jiàn)下圖。別問(wèn)我這圖是哪來(lái)的，如果不幾道，佛山無(wú)銀腳，出門右拐重看第一章吧~

4.

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

最后說(shuō)下如何使用ADO執(zhí)行SQL語(yǔ)句。

別走開(kāi)，喝杯水先我，咕咚咕咚……

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

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

通常我們使用ADO的Execute語(yǔ)句或Recordset對(duì)象執(zhí)行SQL語(yǔ)句，有以下幾種方法。

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 姓名,成績(jī) FROM [Sheet1$] WHERE 成績(jī)>=80'

    'Sql語(yǔ)句，查詢Sheet1表成績(jī)大于80……姓名和成績(jī)的記錄

    Set rst = cnn.Execute(Sql)

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

    '以上是第三步，編寫(xiě)并使用SQL語(yǔ)句

    '

    [d:e].ClearContents

    '清空[d:e]區(qū)域的值

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

    '利用fields屬性獲取所有字段名，fields包含了當(dāng)前記錄有關(guān)的所有字段,fields.count得到字段的數(shù)量

    '由于Fields.Count下標(biāo)為0，又從0開(kāi)始遍歷，因此總數(shù)-1

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

    Next

    Range('d2').CopyFromRecordset rst

    '使用單元格對(duì)象的CopyFromRecordset方法將rst內(nèi)容復(fù)制到D2單元格為左上角的單元格區(qū)域

    '以上是第四步，將SQL查詢結(jié)果和字段名寫(xiě)入表格指定區(qū)域

    '

    cnn.Close

    '關(guān)閉鏈接

    Set cnn = Nothing

    '釋放內(nèi)存

End Sub

事實(shí)上，也可以不使用rst對(duì)象，只使用Execute語(yǔ)句，直接將SQL查詢的結(jié)果放入指定單元格區(qū)域。該方法的優(yōu)點(diǎn)是代碼簡(jiǎn)潔，缺點(diǎn)是只獲得記錄，沒(méi)有字段名，或者說(shuō)沒(méi)有標(biāo)題欄，代碼如下。

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 姓名,成績(jī) FROM [Sheet1$] WHERE 成績(jī)>=80'

    'Sql語(yǔ)句，查詢Sheet1表成績(jī)大于80……姓名和成績(jī)的記錄

    [d:e].ClearContents

    '清空[d:e]區(qū)域內(nèi)容

    Range('d2').CopyFromRecordset cnn.Execute(Sql)

    'Execute語(yǔ)句先執(zhí)行SQL語(yǔ)句

    '使用單元格對(duì)象的CopyFromRecordset方法將SQL查詢到的內(nèi)容復(fù)制到D2單元格為左上角的單元格區(qū)域

    '此法代碼簡(jiǎn)潔，但未能獲取標(biāo)題欄信息

    '以上是第三步，執(zhí)行SQL語(yǔ)句并將數(shù)據(jù)讀入表格指定區(qū)域

    '

    cnn.Close

    '關(guān)閉鏈接

    Set cnn = Nothing

    '釋放內(nèi)存

End Sub

除了使用Execute語(yǔ)句，也可以使用Recordset執(zhí)行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，并創(chuàng)建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 姓名,成績(jī) FROM [Sheet1$] WHERE 成績(jī)>=80'

    'Sql語(yǔ)句，查詢Sheet1表成績(jī)大于80……姓名和成績(jī)的記錄

    rst.Open Sql, cnn, 1, 3

    'rst執(zhí)行SQL語(yǔ)句，并將查詢結(jié)果放入記錄集

    '以上是第三步，編寫(xiě)并執(zhí)行SQL

    '

    [d:e].ClearContents

    '清空[d:e]區(qū)域的值

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

    '利用fields屬性獲取所有字段名，fields包含了當(dāng)前記錄有關(guān)的所有字段,fields.count得到字段的數(shù)量

    '由于Fields.Count下標(biāo)為0，又從0開(kāi)始遍歷，因此總數(shù)-1

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

    Next

    Range('d2').CopyFromRecordset rst

    '使用單元格對(duì)象的CopyFromRecordset方法將rst內(nèi)容復(fù)制到D2單元格為左上角的單元格區(qū)域

    '以上是第四步，將SQL查詢結(jié)果和字段名稱寫(xiě)入表格指定區(qū)域

    '

    rst.Close

    '關(guān)閉記錄集rst

    cnn.Close

    '關(guān)閉鏈接

    Set rst = Nothing

    Set cnn = Nothing

    '釋放內(nèi)存

End Sub

打個(gè)響指，關(guān)于第三個(gè)Recordset方法，目前知道有這事就好，至于它的語(yǔ)法，以及Recordset和Execute之間的區(qū)別，因?yàn)闋砍兜礁鄬I(yè)的術(shù)語(yǔ)和知識(shí)，所以等我們聊完SQL常用語(yǔ)句后，詳聊ADO時(shí)再說(shuō)。

本節(jié)內(nèi)容我們只需要掌握使用Execute語(yǔ)句執(zhí)行SQL，以及獲取字段名……也就是Execute方法（一）的代碼……就OK。

5.

老干部臉，嘶啞嗓音：

各位親，以上內(nèi)容看不懂的段落請(qǐng)直接跳過(guò)，不要糾結(jié)，不要給自己制造心理障礙，啊，這東西好難啊，我不會(huì)……看我眼睛，再重復(fù)一遍，看不懂的段落請(qǐng)直接跳過(guò)，這是一個(gè)好習(xí)慣。等整個(gè)系列進(jìn)行了大概一半左右，再回頭來(lái)看，當(dāng)初覺(jué)得困阻的地方原來(lái)也就那么回事……

仰臉，擺擺手，經(jīng)驗(yàn)之談，都是經(jīng)驗(yàn)之談……心痛。

呵，總結(jié)一下：

對(duì)于新手而言，本章的重點(diǎn)是知道三步走發(fā)展戰(zhàn)略，以及懂得復(fù)制Execute方法（一）的代碼執(zhí)行SQL語(yǔ)句，僅此而已，其它？擺擺我的小手，看過(guò)就算，大概了解下，留個(gè)印象，以后再見(jiàn)面好說(shuō)話也就行了。

最后，還是留個(gè)練手題吧：

由于數(shù)據(jù)源并不存在標(biāo)題行（HDR），且需要將匯入模式（IMEX）修改為讀寫(xiě)，所以請(qǐng)將下面代碼中的鏈接字符串修改完善，并運(yùn)行成功。

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

安~別熬夜~擁抱~

一碼不掃，
可以掃天下？

ExcelHome

VBA編程學(xué)習(xí)與實(shí)踐