馬紅 江蘇省廣電有線信息網(wǎng)絡(luò)股份有限公司南京分公司

1.背景

由于Excel軟件具有獲取方便，容易操作等特點(diǎn)，使得其在當(dāng)今社會(huì)中幾乎成了一種與計(jì)算機(jī)、萬(wàn)用手冊(cè)一樣的必備工具。Excel除了可以用來(lái)制作各種華麗的表格和圖標(biāo)，進(jìn)行簡(jiǎn)單的運(yùn)算之外，還可以當(dāng)成是“管理個(gè)人數(shù)據(jù)庫(kù)的工具”，并可以以此為基礎(chǔ)進(jìn)行數(shù)據(jù)分析，指導(dǎo)實(shí)際工作。日常工作中，有很多相對(duì)重復(fù)的表格或是數(shù)據(jù)操作類的工作產(chǎn)生了很多工作量，占用了太多的寶貴時(shí)間，如果能用VBA來(lái)解決這個(gè)問題，讓原本重復(fù)繁雜的工作做到一鍵搞定，就能大大提高我們的工作效率。

Excel雖然也可以單獨(dú)當(dāng)成數(shù)據(jù)庫(kù)來(lái)使用，不過將其和Oracle數(shù)據(jù)庫(kù)專用應(yīng)用程序結(jié)合在一起，可以構(gòu)建出處理大量數(shù)據(jù)的大型系統(tǒng)。日常工作中，數(shù)據(jù)庫(kù)的操作和執(zhí)行具有一定的局限性，需要一定的專業(yè)技能，如果能跟Excel結(jié)合，會(huì)非常明顯的擴(kuò)大應(yīng)用場(chǎng)景，增強(qiáng)工作效率，能使得一些原本非常繁瑣而專業(yè)的事情變的更加方便可行。

2.ADO的概念和架構(gòu)

通過VBA連接到Oracle等外部數(shù)據(jù)庫(kù)進(jìn)行數(shù)據(jù)控制，需要使用到ADO。

2.1 ADO的概念

ADO是ActiveX Data Object的簡(jiǎn)稱，是一個(gè)用來(lái)存取數(shù)據(jù)用的ActiveX控制項(xiàng)的對(duì)象，ADO應(yīng)用非常方便，它在設(shè)計(jì)上可以用幾乎相同的程序代碼與各種數(shù)據(jù)庫(kù)進(jìn)行存取。

通過ADO的使用，使得VBA和數(shù)據(jù)庫(kù)的交互和通過VBA直接進(jìn)行數(shù)據(jù)庫(kù)記錄的讀取等操作得以實(shí)現(xiàn)。ADO在控制外部數(shù)據(jù)庫(kù)的時(shí)候，擔(dān)任了數(shù)據(jù)庫(kù)和操作數(shù)據(jù)應(yīng)用程序之間的中介角色，如圖1所示。

2.2 ADO的框架

通過OLE DB Provider原件來(lái)存取各個(gè)數(shù)據(jù)庫(kù)，對(duì)ADO而言如同英漢詞典一樣，就算數(shù)據(jù)庫(kù)的類型有所改變，又或是新的數(shù)據(jù)庫(kù)類型出現(xiàn)，只要準(zhǔn)備好OLE DB Provider字典就可以從ADO進(jìn)行存取，所以說ADO就是一種數(shù)據(jù)控制的通用接口，通過ADO連接到數(shù)據(jù)庫(kù)的框架如圖2所示。

3.連接數(shù)據(jù)庫(kù)

圖1 ADO作為Excel與外部數(shù)據(jù)庫(kù)之間的中介

圖2 通過ADO連接到數(shù)據(jù)庫(kù)的框架

圖3 ADO的簡(jiǎn)單對(duì)象模型

使用ADO將Excel和外部數(shù)據(jù)庫(kù)連接，需要使用Connection對(duì)象。通過Connection對(duì)象的Open方法確立和數(shù)據(jù)庫(kù)的連接。形象的說，Connection對(duì)象就是一種類似于將Excel和外部數(shù)據(jù)庫(kù)連接在一起的線。

3.1 制作連接字符串

在使用Connection對(duì)象確定與特定數(shù)據(jù)庫(kù)連接之前，我們需要制作連接字符串，連接字符串包括指定數(shù)據(jù)庫(kù)地址、數(shù)據(jù)庫(kù)類型、數(shù)據(jù)庫(kù)用戶名稱和登錄密碼等，這些內(nèi)容需要準(zhǔn)確無(wú)誤。

連接數(shù)據(jù)庫(kù)的字符串有基本的格式，基本格式為Provider=<使用的 OLE DB Provider名稱>；Data source=<對(duì)外部DB的路徑>。其中Provider名稱根據(jù)要連接的不同的數(shù)據(jù)庫(kù)類型去確定相應(yīng)的名稱，不容類型的數(shù)據(jù)庫(kù)對(duì)應(yīng)不同的OLE DB Provider。表1列舉出幾種具有代表性數(shù)據(jù)庫(kù)引擎的字符串。

如表1 數(shù)據(jù)庫(kù)引擎對(duì)應(yīng)的字符串

數(shù)據(jù)庫(kù)引擎 字符串

Jet4.0(Access2000以后) Provider=Microsoft.jet.

OLEDB.4.0

SQL serve Provider=SQLOLEDB.1

Oracle Provider=MSDAORA.1

ODBC驅(qū)動(dòng)器 Provider=MSDASQL.1

從表1可以看出，通過VBA去連接Oracle數(shù)據(jù)庫(kù)，選取字符串MSDAORA.1。再結(jié)合確切的數(shù)據(jù)庫(kù)用戶名和用戶登錄密碼，依據(jù)這些基本信息，根據(jù)既定的格式，可以寫出連接字符串，如下所示：

表1 數(shù)據(jù)庫(kù)引擎對(duì)應(yīng)的字符

"Provider=MSDAORA.1;password=用戶登錄密碼;user id=用戶名;data source=數(shù)據(jù)庫(kù)名;Persist Security Info=True;"此連接字符串存放于變量定義后，數(shù)據(jù)庫(kù)連接指令前。

3.2 連接數(shù)據(jù)庫(kù)

連接字符串編寫好并且確認(rèn)無(wú)誤后，應(yīng)用基本語(yǔ)法“Connection對(duì)象.Open ”進(jìn)行和外部數(shù)據(jù)庫(kù)的連接。先聲明一個(gè)Connection對(duì)象為conn，如：Dim conn AS New ADODB.Connection；

定義連接字符串strConn ="Provider=MSDAORA.1;password=用戶登錄密碼;user id=用戶名;data source=數(shù)據(jù)庫(kù)名;Persist Security Info=True;"

連接數(shù)據(jù)庫(kù)的基本格式為：conn.Open strConn，如圖4所示。

4.操作數(shù)據(jù)庫(kù)

利用Connection對(duì)象和外部數(shù)據(jù)取得連接后，接下來(lái)從獲得連接的數(shù)據(jù)庫(kù)取得數(shù)據(jù)，需要使用Recordset對(duì)象。Recordset對(duì)象是一種存儲(chǔ)從數(shù)據(jù)庫(kù)中所取得數(shù)據(jù)記錄的容器。

圖4 連接數(shù)據(jù)庫(kù)的基本格式

4.1 將Excel數(shù)據(jù)更新或插入到數(shù)據(jù)庫(kù)表中

使用ADO和SQL語(yǔ)句可以更容易更靈活的獲取所需的數(shù)據(jù)。正常情況下，需要編寫專業(yè)的SQL腳本在數(shù)據(jù)庫(kù)執(zhí)行，以獲取想要的數(shù)據(jù)。在使用VBA跟外部數(shù)據(jù)連接的情況下，可以把需要執(zhí)行的SQL語(yǔ)句放在相應(yīng)的單元格中，相當(dāng)于給單元格賦予內(nèi)容，而內(nèi)容就是具體的SQL腳本。對(duì)于比較復(fù)雜的SQL語(yǔ)句，可以合理的分開放在多個(gè)單元格中。如果SQL非常簡(jiǎn)短，也可以直接將SQL語(yǔ)句寫在VBA程序中。做好取數(shù)據(jù)的關(guān)鍵，第一步是要保證調(diào)取數(shù)據(jù)的SQL腳本準(zhǔn)確無(wú)誤，再根據(jù)習(xí)慣和復(fù)雜程度把SQL放在Excel的單元格或是直接寫入VBA程序。

4.2 將表中的數(shù)據(jù)讀入Excel工作表中

在Recordset對(duì)象中設(shè)置外部數(shù)據(jù)庫(kù)特定數(shù)據(jù)表的引用，可以進(jìn)行更詳細(xì)的數(shù)據(jù)庫(kù)表控制。首先對(duì)特定數(shù)據(jù)表數(shù)據(jù)進(jìn)行處理的時(shí)候，可以引用最前面或者最后面乃至任意一處的記錄，這里涉及到移動(dòng)目標(biāo)記錄的方法。表2給出了移動(dòng)目標(biāo)記錄的方法。當(dāng)一直重復(fù)從一個(gè)位置往后移動(dòng)或者往前移動(dòng)時(shí)，有可能超過最前面或者最后面的記錄，然而對(duì)于超過第一條或者最后一條記錄的記錄，當(dāng)進(jìn)行取值的時(shí)候就會(huì)報(bào)錯(cuò)。因此需要當(dāng)前取值不超出最前面和最后面的位置，這可以用BOF或EOF來(lái)判斷，通過這樣的組合去編寫循環(huán)過程會(huì)方便記錄的導(dǎo)出。

表2 移動(dòng)目標(biāo)記錄的方法

表3 判斷記錄位置的屬性

取得外部數(shù)據(jù)庫(kù)數(shù)據(jù)的基本邏輯就是把外部數(shù)據(jù)庫(kù)的數(shù)據(jù)寫入相應(yīng)指定的單元格，此時(shí)需要知道數(shù)據(jù)庫(kù)結(jié)果集的列數(shù)和對(duì)應(yīng)的列名，基本語(yǔ)法為：

worksheet.Cells(i，j) = RecordsetObject.Fields(count).

Value

或者是：

按照《建筑節(jié)能工程施工質(zhì)量驗(yàn)收規(guī)范》（GB50411-2007）中規(guī)定：通風(fēng)與空調(diào)工程安裝完成后，在聯(lián)合試運(yùn)轉(zhuǎn)及調(diào)試結(jié)束后應(yīng)進(jìn)行系統(tǒng)節(jié)能性能的檢測(cè)，其規(guī)定了通風(fēng)與空調(diào)系統(tǒng)節(jié)能性能檢測(cè)主要項(xiàng)目為室內(nèi)溫度、各風(fēng)口的風(fēng)量、系統(tǒng)的總風(fēng)量、空調(diào)機(jī)組的水流量、空調(diào)系統(tǒng)冷熱水、冷卻水總流量，檢測(cè)批次為各風(fēng)口的風(fēng)量、系統(tǒng)的總風(fēng)量、空調(diào)機(jī)組的水流量抽檢系統(tǒng)數(shù)量的10%，且不得少于1個(gè)系統(tǒng)?？照{(diào)系統(tǒng)冷熱水、冷卻水總流量為全數(shù)檢測(cè)。

w o r k s h e e t.C e l l s(i，j) = R e c o r d s e t O b j e c t.Fields(fieldName)，

然后再配合記錄的移動(dòng)來(lái)導(dǎo)出所有數(shù)據(jù)庫(kù)結(jié)果集。

以上討論的都是取得結(jié)果集的模式，有時(shí)在查詢數(shù)據(jù)的類型中，除了在執(zhí)行后返回結(jié)果組的查詢外，還有一種在既存數(shù)據(jù)表中進(jìn)行數(shù)據(jù)更改的動(dòng)態(tài)查詢，這種不需要取得結(jié)果集的查詢模式，就必須要使用Command對(duì)象。

4.3 變更外部數(shù)據(jù)庫(kù)的記錄

有時(shí)所要做的不僅是對(duì)外部數(shù)據(jù)庫(kù)的記錄原封不動(dòng)的導(dǎo)出，還需要對(duì)外部數(shù)據(jù)庫(kù)的記錄進(jìn)行變更，比如添加記錄，刪除記錄，更新某些特定條件的記錄等。在進(jìn)行記錄的變更前，需要注意的是要指定Recordset對(duì)象的LockType屬性，默認(rèn)狀態(tài)下設(shè)置的是AdLockReadOnly，表示只讀，此時(shí)無(wú)法進(jìn)行記錄的添加變更和刪除。將這個(gè)屬性更改為AdLockPessimistic，以允許打開相應(yīng)的數(shù)據(jù)表進(jìn)行相應(yīng)的變更操作。

在外部數(shù)據(jù)庫(kù)的任意數(shù)據(jù)表中添加記錄時(shí)，使用Recordset對(duì)象的AddNew方法；刪除數(shù)據(jù)表記錄時(shí)，使用Delete方法；更改Recordset對(duì)象中的記錄值時(shí)，使用Update方法。

4.4 操作外部數(shù)據(jù)庫(kù)的基本步驟演示

以下對(duì)應(yīng)于操作外部數(shù)據(jù)庫(kù)的情景，立足一個(gè)簡(jiǎn)單的實(shí)例，給出VBA操作外部數(shù)據(jù)庫(kù)基本步驟的演示。

（1）聲明并定義Recordset對(duì)象；

（2）將需要導(dǎo)入的數(shù)據(jù)表名稱與使用的連接指定為Open方法的參數(shù)；

（3）將數(shù)據(jù)表的數(shù)據(jù)導(dǎo)入到定義的Recordset對(duì)象中；

（4）使用CopyFromRecordset方法將導(dǎo)入的數(shù)據(jù)寫到相應(yīng)的單元格中，使用此方法時(shí)指定要導(dǎo)出數(shù)據(jù)所在的Recordset對(duì)象為參數(shù)；

（5）數(shù)據(jù)導(dǎo)出后，將使用過的Recordset對(duì)象和Connection對(duì)象進(jìn)行釋放。

列舉一個(gè)例子來(lái)說明。假設(shè)Excel表格的sheet1中的單元格A1到A2中存放SQL腳本，定義的Recordset對(duì)象rs，定義的Connection對(duì)象為conn，則基本語(yǔ)法如下：

5.工作表另存工作簿

有時(shí)需要通過VBA控制數(shù)據(jù)庫(kù)一次性執(zhí)行多段相互平行的SQL腳本，為方便起見，會(huì)把外部數(shù)據(jù)庫(kù)的記錄轉(zhuǎn)存為同一個(gè)工作簿的多個(gè)工作表，然后再進(jìn)一步把多個(gè)工作表另存為多個(gè)工作簿。要完成此項(xiàng)工作，首先要明確工作簿的存放目錄，然后要明確工作簿的類型，比如是csv類型或者xlsx類型等。把文件保存為csv格式的基本語(yǔ)法如下：ActiveWorkbook.SaveAs Filename:="文件存放目錄"& "文件名 " & ".csv"， FileFormat:=xlCSV

6.總結(jié)

Excel的VBA與Oracle數(shù)據(jù)庫(kù)結(jié)合交互進(jìn)行數(shù)據(jù)處理的方法的應(yīng)用日趨廣泛，在數(shù)據(jù)分析中起到至關(guān)重要的作用，突破了Oracle數(shù)據(jù)庫(kù)對(duì)相關(guān)使用人員專業(yè)性要求高的缺點(diǎn)，極大方便了報(bào)表的自動(dòng)生成。只需把SQL腳本封裝到Excel當(dāng)中，再通過ADO、Connection對(duì)象、Recordset對(duì)象等專業(yè)工具，就可以實(shí)現(xiàn)通過VBA操作外部數(shù)據(jù)庫(kù)，并且能夠?qū)?shù)據(jù)庫(kù)的執(zhí)行結(jié)果記錄進(jìn)行選擇，復(fù)制以及更改的操作，從而實(shí)現(xiàn)了通過Excel的VBA把數(shù)據(jù)庫(kù)的執(zhí)行結(jié)果按照預(yù)定的方式展現(xiàn)出來(lái)。