Office Space:撰寫 Microsoft Office 應用程式指令碼的秘訣

Office Space

歡迎蒞臨 Office Space 專欄,這裡提供處理 Microsoft® Office 應用程式指令碼的秘訣。每週二和週四我們將刊出新的秘訣,若要參閱以前討論過的秘訣,請造訪 Office Space Archive (Office Space 過往文件)。您有關於系統管理指令碼方面的問題嗎?請將電子郵件傳送到 scripter@microsoft.com。我們無法保證能夠逐一回答每個問題,不過我們會盡力而為。

使用 ADO 查詢 Excel 試算表

大約一個月前,美國哥倫比亞廣播公司頗受歡迎的單元喜劇《人人都愛雷蒙德》(Everybody Loves Raymond) 終於落幕。這個節目在美國數年蟬聯收視率冠軍,不過我們 Scritping Guys 沒有一個看過這個節目 (想當然爾,Scripting Guy 對主流文化都有點慢半拍,沒看過《美國偶像》(American Idol) 或《我要活下去》(Survivor),所以裡面的角色到現在還弄不清楚。現在《人人都愛雷蒙德》已經播完了,電視台得找新的節目填補時段,全世界的人也在等著新的節目。接下來觀眾會瘋什麼?

我們一致認為應該提名 Microsoft Excel,畢竟,Excel 有什麼地方不值得人愛的呢?需要傳統的試算表程式,幫您打點預算、會計和其他理財工作?Excel 可以勝任。需要製作圖表和圖形?Excel 也沒問題。至於建立組織圖?統計?交給 Excel 準沒錯!Excel 無所不能!

還不信嗎?「Excel 真的無所不能嗎?」有些人心裡可能這麼想,「既然它那麼能幹,Excel 有能耐扮演普通檔案資料庫的角色嗎?可以使用 ADO 存取 Excel 試算表內的資料嗎?可以在 Excel 裡進行 SQL 查詢並傳回資料嗎?」

這麼說好了,《Office Space》專欄既然能網際網路上刊出,就表示對裡頭的資料有相當的信心。您當然可以把 Excel 當作普通檔案資料庫使用,並且可以透過 ADO 和 SQL 查詢存取,這也正是今天專欄的重點。

我們拿一個簡單的試算表為例,並說明如何使用 ADO (ActiveX Data Objects) 存取裡頭的資料。假設有一份很基本的試算表叫做 C:\Scripts\Test.xls,裡面共有兩欄,一個標示為 Name,另一個標示為 Number。為了保證您能順利從 Excel 試算表進行資料庫查詢,請務必按照下列方式安排試算表:把第一列當成標題列,從第二列開始輸入資料,不要跳過任何一列或欄。為了簡化編碼,標題請勿包含空格,例如使用 SocialSecurityNumber 當成欄名,而不要使用包含空格的 Social Security Number,這樣可以避免很多麻煩 (這項原則適用任何類型的資料庫,不只是 Excel)。

設定完後,試算表會變成這樣:

Microsoft Excel


沒錯,建立這個範例試算表的確花了一些時間,這只是今天秘訣分享的開始。

現在重點來了,要怎樣使用 ADO 存取資料庫呢?其中一種方法是使用下列程式碼:


On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop


指令碼前半段只是定義一些常數並建立兩個物件 (ADODB.ConnectionADODB.Recordset),分別用來連接資料來源和從中擷取資料。這大部分是 ADO 指令碼裡面可以現用的程式碼範本。既然有現成的程式碼可以用,我們就不詳述前半段指令碼。如果您想進一步瞭解,可以閱讀《Microsoft Windows 2000 Scripting Guide》的<Working with Databases>(英文) 一節。

現在來看看實際開啟與 Excel 試算表連結的這小段指令碼:


objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";"


這大部分又是現成的程式碼。您只需要注意用來指定試算表路徑的 Data Source 這小部分。

注意:如果您試算表的路徑包含空格,會不造成 ADO 指令碼無法順利使用文字檔 (詳情請看 嗨,Scripting Guy!專欄)這您倒不用擔心,只要把整個檔案路徑包括空格列出來就行了:

Data Source=C:\Scripts\My Spreadsheet.xls


順便提醒,看到 Excel 8.0 時請不要手癢把版本改成您目前電腦上使用的 Excel 版本。這裡 Excel 8.0 指的並不是您目前使用的 Excel 版本,而是供 ADO 提供者用來存取 Excel 之用。將提供者保留為 Excel 8.0,保證不會出事情。

HDR=Yes 程式碼不過是用來指出範例中的試算表有標題列。如果試算表沒有標題列,要把 HDR 設定成 No。但希望讀者聽我們 Scripting Guy 的建議,要養成設定標題列的習慣,至於大家聽不聽得進去我們就不知道啦。

連到資料來源後,我們可以用 SQL 查詢擷取資料來源內存放的資訊。接著用下面這套程式碼傳回包含試算表內所有列的資料錄集:


objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText


這裡也包含很多 ADO 現成指令碼,現在只需要注意的是實際執行 SQL 查詢的部分:


Select * FROM [Sheet1$]


這是一項標準的 SQL 查詢,選取資料庫 (工作表) 內所有的欄位 (直欄)。請注意,我們在查詢裡頭指定了一個工作表的名稱,就跟先前指定一個表格名稱以便連接到資料庫的方式一樣。還有,工作表名稱用方括弧包住,且實際名稱 (Sheet1) 後面附加了一個 $。您撰寫自己的 ADO 指令碼時別忘了這兩個重點,才能存取試算表內的資料。

傳回的資料錄集跟連接到 SQL Server 得到的結果是一樣的。所以我們可使用這幾行指令碼,回傳資料錄集裡頭每一項資料的 Name 和 Number,剛好對應試算表裡頭的每一列:


Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop


執行指令碼後,應該會得到一個像下面一樣的報告:


A 1
B 1
C 2
D 2
E 1
F 1


是不賴,但有人會想:其實用 Excel 內含的指令碼也可以達到一樣的目的,不是嗎?沒錯,所以在本例中其實根本不需要 ADO。

那…,這篇專欄不是白寫了嘛!(嗯,其實每篇專欄都有人這麼問。)是,我們不否認,如果您只想回應試算表裡頭每一行,大可不需要大費周章 (除非您覺得寫 ADO 比 Excel 指令碼還要順手)。但是,假設我們只想回應列中 Number 為 2 的資料,標準的 Excel 指令碼需要一列列的跑,檢查 Number 欄位是不是等於 2,藉由數值判斷是否要回應資料列。基本上這不難,但如果要查詢的數值超過一欄 (例如要找出財政部裡頭所有職銜為行政助理的使用者),相對比較麻煩。

而使用 ADO,不用一行行查試算表,就可以得出一樣的結果。而且用的指令碼就跟上面示範的幾乎一樣,只要改 SQL 查詢就可以了。我們只要找 Number 等於 2 的列,所以 SQL 查詢得這麼寫:


objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText


這樣就得了,前面的查訓會得到下列資料:


C 2
D 2


為何使用 ADO?是這樣的,您可以使用 SQL 查詢,輕輕鬆鬆傳回試算表內含資料的子集。所以您可能會需要 ADO 存取 Excel 檔案。

順便一提,下面是只傳回 Number 欄等於 2 的完整指令碼:


On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\Test.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$] Where Number = 2", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _
        objRecordset.Fields.Item("Number")
    objRecordset.MoveNext
Loop


對了,您可能也想知道,ADO 的功能不只擷取資料。日後專欄將示範如何使用 ADO 把資料寫入試算表。

好啦,還有誰懷疑 Excel 為何人見人愛?

顯示: