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

發行項
09/07/2010

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.Connection 和 ADODB.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 為何人見人愛？

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

使用 ADO 查詢 Excel 試算表

其他資源