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

Office Space

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

從 Microsoft Outlook 擷取週期性約會的清單

Scripting Guy 多半不擅於應付截止時間,也不擅於處理日期這種事情。那是因為我們太懶還是太沒責任感了嗎?當然不是,這都要怪 Microsoft Outlook。雖然我們總是盡量提前規劃,還是免不了因為忘記週期性會議和約會而大亂陣腳。我們需要一種快速簡單的方法,調出所有週期性會議 (包括所有會議的日期、時間和地點) 的清單。不過,據我們所知,Outlook 並未提供任何既快速又簡便的方法,可以擷取週期性約會的相關資訊。所以囉,別怪我們忘了開會而錯過截止日期,要怪就怪 Outlook。

注意:沒錯,這剛好與 Scripting Guy 的座右銘完全吻合:責任永遠是別人的,不是我們的,就在那裡,在別人辦公室裡。


什麼意思?您是說我們應該自力救濟,別光靠 Microsoft Outlook 為我們搞定所有大小事嗎?難不成您要我們撰寫一段「指令碼」,傳回這項資訊嗎?這個嘛 … 是有點不可思議啦,不過還真的行得通呢:


Const olFolderCalendar = 9

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

Set colItems = objFolder.Items

Set colFilteredItems = colItems.Restrict("[IsRecurring] = TRUE")

For Each objItem In colFilteredItems
    Set objPattern = objItem.GetRecurrencePattern
    If objPattern.PatternEndDate > Now Then
        Wscript.Echo "Meeting name: " & objItem.Subject
        Wscript.Echo "Duration: " & objItem.Duration & " minutes"
        Wscript.Echo "Location: " & objItem.Location
        Wscript.Echo
    End If
Next


這個指令碼先建立一個常數 olFolderCalendar,並將其值設為 9;在我們告訴 Outlook 要從哪一個資料夾 (行事曆) 擷取資訊時,這個常數才會派上用場。接下來就是為 Outlook.Application 物件建立一個執行個體,然後利用 GetNamespace 方法繫結到 MAPI 命名空間。(沒錯,即使您只能繫結到 MAPI 這個命名空間,還是需要這行程式碼)。接著我們就用GetDefaultFolder 方法繫結到 Outlook 行事曆:


Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)


(看到沒?我們說過會用到常數 olFolderCalendar 的!)

繫結到 [行事曆] 之後,就可以利用下面這行程式碼來擷取 [行事曆] 資料夾中所有的項目集合 (萬一不清楚的話,只要記住所有項目都是 AppointmentItem 物件的執行個體):


Set colItems = objFolder.Items


說得好:現在所有的約會和會議都在 [行事曆] 裡面了。不過,我們要的是「週期性」約會和會議的集合。沒關係,我們可以利用 Restrict 方法篩選這個集合,濾掉非週期性的約會和會議。下面就是我們的做法:


Set colFilteredItems = colItems.Restrict("[IsRecurring] = TRUE")


如您所見,我們建立一個新的集合 (名叫 colFilteredItems),其步驟是呼叫 Restrict 方法,然後代入下面這個參數:


"[IsRecurring] = TRUE"


太厲害了,您已經遠遠超越我們了:其實,IsRecurring 是 AppointmentItem 物件的屬性,這個屬性負責判斷特定的約會頻率是否超過一次以上。如果 IsRecurring 為 True,就表示它是週期性約會;如果 IsRecurring 為 False,就表示僅此一次 (當然我們最愛這種會議了)。

注意:還想進一步瞭解 Restrict 方法嗎?那就看看之前刊登過的這篇《Office Space 專欄》(英文) 。


接下來我們要設定一個 For Each 迴圈,逐一執行篩選過的項目集合;每一個會議我們都要回報 SubjectDurationLocation。接著就得到如下所示的資料:


Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)


很酷吧?

喔,您不同意嗎?這可不是故意抹黑唷 (怎麼說那也是我們的心血結晶嘛),不過它只能算差強人意啦。雖然它告訴我們有好幾個週期性約會,可是卻沒說這些約會到底在什麼時候進行:沒日期,沒時間,什麼都沒有。難怪我們會這麼疲於奔命的規劃和趕赴截止日期了!您看,我們早就說了,這都是 Outlook 的錯!

對了,還是自力救濟那句老話。好,讓我們試試看能不能解決這個問題。有關週期性約會的時間 (以及頻率) 的資訊,是儲存在另一個物件當中:RecurrencePattern 物件。我們必須呼叫 GetRecurrencePattern 方法,來擷取與特定約會相關的 RecurrencePattern 物件。這樣才能回應約會的頻率 (RecurrenceType)、每個約會的開始日期 (StartTime)、同系列約會的開始日期 (PatternStartDate) 以及同系列約會的結束日期 (PatternEndDate) 等資訊。下面就是修改後的指令碼:


Const olFolderCalendar = 9

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

Set colItems = objFolder.Items

strFilter = "[IsRecurring] = TRUE"

Set colFilteredItems = colItems.Restrict(strFilter)

For Each objItem In colFilteredItems
    Set objPattern = objItem.GetRecurrencePattern
    Wscript.Echo "Meeting name: " & objItem.Subject
    Wscript.Echo "Duration: " & objItem.Duration & " minutes"
    Wscript.Echo "Location: " & objItem.Location
    Wscript.Echo "Recurrence type: " & objPattern.RecurrenceType
    Wscript.Echo "Start time: " & objPattern.StartTime
    Wscript.Echo "Start date: " & objPattern.PatternStartDate
    Wscript.Echo "End date: " & objPattern.PatternEndDate
    Wscript.Echo
Next


執行這個指令碼之後,會得出如下所示的資訊:


Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000
Recurrence type: 1
Start time: 1:00:00 PM
Start date: 5/27/2004
End date: 12/31/4500 11:59:00 PM

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)
Recurrence type: 1
Start time: 11:00:00 AM
Start date: 6/25/2004
End date: 11/25/2005


我們在原始指令碼中加了兩樣新的東西。首先,我們加了下面這一行程式碼,來擷取與每個約會相關的 RecurrencePattern 物件:


Set objPattern = objItem.GetRecurrencePattern


其次,我們加了下面這三行程式碼,來回應 RecurrencePattern 物件的屬性。請注意,在這三行中,我們用的是物件參考 objPattern,而非物件參考 objItem。因為我們處理的是 RecurrencePattern 物件,而不是 AppointmentItem 物件。是啦,的確很容易混淆,不過您得習慣才行。

下面就是回應 RecurrencePattern 物件屬性所用的三行程式碼: 


Wscript.Echo "Start time: " & objPattern.StartTime
Wscript.Echo "Start date: " & objPattern.PatternStartDate
Wscript.Echo "End date: " & objPattern.PatternEndDate


信不信由您,我們走上正軌了。舉個例說,現在我們已經知道這些約會的頻率了;唯一的問題就是 RecurrenceType 被回報為下列整數值:

常數

olRecursDaily

0

olRecursMonthly

2

olRecursMonthNth

3

olRecursWeekly

1

olRecursYearly

5

olRecursYearNth

6


這意思就是說,我們必須再修改指令碼一次。這一次我們要用 Select Case 區塊,將 RecurrenceType 轉換為字串值。Select Case 區塊如下所示:


Select Case objPattern.RecurrenceType
    Case 0 Wscript.Echo "Recurs daily."
    Case 1 Wscript.Echo "Recurs weekly."
    Case 2 Wscript.Echo "Recurs every N months."
    Case 3 Wscript.Echo "Recurs monthly."
    Case 5 Wscript.Echo "Recurs yearly."                
    Case 6 Wscript.Echo "Recurs every N years."
End Select


在修改後的指令碼中,我們不再回應 RecurrenceType 的值,而是先檢查 RecurrenceType 的值,然後再回應一個更有用的字串值。比方說,如果 RecurrenceType 等於 1,我們就回應「每週一次」訊息。這就是下面這行程式碼所做的動作:


Case 1 Wscript.Echo "Recurs weekly."


執行這個經過修改的指令碼之後,會得出如下所示的輸出:


Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000
Recurs weekly.
Start time: 1:00:00 PM
Start date: 5/27/2004
End date: 12/31/4500 11:59:00 PM

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)
Recurs weekly.
Start time: 11:00:00 AM
Start date: 6/25/2004
End date: 11/25/2005


現在只漏了一樣東西:開會的確實日期/星期幾。老實說,我們把這個部份留待最後再處理,因為現在已經有點複雜了。每個 RecurrenceType 都有一組屬性可以幫助您精確指出日期和星期幾;您可以在《Microsoft Outlook VBA Language Reference》(英文) 中,找到 RecurrenceTypes 及其相關屬性的清單。比方說,每週進行的約會具有下列兩個相關屬性:

  • Interval 會告訴您開會的頻率 (每週、每兩週、每三週等等)。

  • DayofWeekMask 會告訴您在當週星期幾 (星期一、星期二、星期三等等) 開會。

跟 RecurrenceType 一樣,這些屬性多半會傳回整數值;例如,DayOfWeekMask 會傳回下列其中一值:

常數

olSunday

1

olMonday

2

olTuesday

4

olWednesday

8

olThursday

16

olFriday

32

olSaturday

64


換句話說,如果要傳回真正有用的資訊,必須將這些整數值全部轉換為我們看得懂的東西。我們就不在這裡詳述每一種轉換作業了,不過我們會在專欄結尾處加上一個指令碼來執行這些轉換作業。

注意:為了簡化程序,我們刻意動了一點手腳,假設所有的會議都只在某一天進行 (例如,每月的第二個星期四)。那麼,在每月的第二個星期四以及每月的星期四進行的會議又該如何呢?我們是可以利用指令碼取得這項資訊,不過程序有點複雜,以後再擇期討論。換句話說,如果您的會議時間表真得這麼古怪,那麼今天討論的指令碼也許能夠提供一些 (但不是全部) 幫助。不過我們可以之後再解決它。


現在先容我們為您示範一個程式碼,這個程式碼可將 DayOfWeekMask 整數轉換為開會日期 (星期幾):


Select Case objPattern.DayOfWeekMask
    Case 1
        strDay = "Sunday"
    Case 2
        strDay = "Monday"
    Case 4
        strDay = "Tuesday"
    Case 8
        strDay = "Wednesday"
    Case 16
        strDay = "Thursday"
    Case 32
        strDay = "Friday"
    Case 64
        strDay = "Saturday"
End Select


當您執行這個指令碼時,會得出如下所示的輸出:


Meeting name: Updated: Scripting Guys Strategy Meeting
Duration: 60 minutes
Location: 43/3000
Occurs every Thursday.
Start time: 1:00:00 PM
Start date: 5/27/2004
End date: 12/31/4500 11:59:00 PM

Meeting name: Updated: Script Center Refresh
Duration: 60 minutes
Location: Conf Room 5/1255 (10)
Occurs every Friday.
Start time: 11:00:00 AM
Start date: 6/25/2004
End date: 11/25/2005


沒錯,好多了。

其實還有其他方法可以解決週期性約會的問題,不過今天講的已經夠多了(別擔心;我們保證您很快會停止頭昏腦脹的感覺)。另外前面也提過,接下來我們要示範一個比較完整的指令碼,傳回 (以及轉換成看得懂的格式) 週期性會議和約會。附帶一提,我們也在指令碼中額外加了一個 If-Then 區塊:


If objPattern.PatternEndDate > Now Then


這只是要檢查週期性約會系列是否已經到期。如果已經到期,PatternEndDate 就不會大於目前日期和時間,而約會也不會顯示在輸出當中。這麼一來,只會得出有關後續會議和約會的資訊。

指令碼如下:


Const olFolderCalendar = 9

Set objOutlook = CreateObject("Outlook.Application")
Set objNamespace = objOutlook.GetNamespace("MAPI")
Set objFolder = objNamespace.GetDefaultFolder(olFolderCalendar)

Set colItems = objFolder.Items

strFilter = "[IsRecurring] = TRUE"

Set colFilteredItems = colItems.Restrict(strFilter)

For Each objItem In colFilteredItems
    Set objPattern = objItem.GetRecurrencePattern
    If objPattern.PatternEndDate > Now Then
        Wscript.Echo "Meeting name: " & objItem.Subject
        Wscript.Echo "Duration: " & objItem.Duration & " minutes"
        Wscript.Echo "Location: " & objItem.Location
                Select Case objPattern.RecurrenceType
            Case 0 
                Wscript.Echo "Recurs daily."
            Case 1 
                If objPattern.Interval = 1 Then
                    Select Case objPattern.DayOfWeekMask
                        Case 1
                            Wscript.Echo "Occurs every Sunday."
                        Case 2
                            Wscript.Echo "Occurs every Monday."
                        Case 4
                            Wscript.Echo "Occurs every Tuesday."
                        Case 8
                            Wscript.Echo "Occurs every Wednesday."
                        Case 16
                            Wscript.Echo "Occurs every Thursday."
                        Case 32
                            Wscript.Echo "Occurs every Friday."
                        Case 64
                            Wscript.Echo "Occurs every Saturday."
                        End Select
                Else
                    Select Case objPattern.DayOfWeekMask
                        Case 1
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                " weeks on Sunday."
                        Case 2
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Monday."
                        Case 4
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Tuesday."
                        Case 8
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Wednesday."
                        Case 16
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Thursday."
                        Case 32
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Friday."
                        Case 64
                            Wscript.Echo "Occurs every " & objPattern.Interval & _
                                 " weeks on Saturday."
                        End Select
                    End If
            Case 2 Wscript.Echo 
               intInstance = objPattern.Instance
                Select Case intInstance
                    Case 1
                        strInstance = "first"
                    Case 2
                        strInstance = "second"
                    Case 3
                        strInstance = "third"
                    Case 4
                        strInstance = "fourth"
                    Case 5
                        strInstance = "fifth"
                    Case 6
                        strInstance = "sixth"
                End Select
                Select Case objPattern.DayOfWeekMask
                    Case 1
                        strDay = "Sunday"
                    Case 2
                        strDay = "Monday"
                    Case 4
                        strDay = "Tuesday"
                    Case 8
                        strDay = "Wednesday"
                    Case 16
                        strDay = "Thursday"
                    Case 32
                        strDay = "Friday"
                    Case 64
                        strDay = "Saturday"
                End Select
                intInterval = objPattern.Interval
                If intInterval = 1 Then
                    Wscript.Echo "Occurs on the " & strInstance & " " & strDay &  _
                        " of each month."
                Else
                    Wscript.Echo "Occurs on the " & strInstance & " " & strDay & _
                        " every " & intInterval & " months."
                End If         
            Case 3 
                If objPattern.Interval = 1 Then
                    Wscript.Echo "Recurs each month."
                Else
                    Wscript.Echo "Recurs every " & objPattern.Interval & " month."
                End If 
                intInstance = objPattern.Instance
                Select Case intInstance
                    Case 1
                        strInstance = "first"
                    Case 2
                        strInstance = "second"
                    Case 3
                        strInstance = "third"
                    Case 4
                        strInstance = "fourth"
                    Case 5
                        strInstance = "fifth"
                    Case 6
                        strInstance = "sixth"
                End Select
                Select Case objPattern.DayOfWeekMask
                    Case 1
                        strDay = "Sunday"
                    Case 2
                        strDay = "Monday"
                    Case 4
                        strDay = "Tuesday"
                    Case 8
                        strDay = "Wednesday"
                    Case 16
                        strDay = "Thursday"
                    Case 32
                        strDay = "Friday"
                    Case 64
                        strDay = "Saturday"
                End Select
                Wscript.Echo "Occurs on the " & strInstance & " " & strDay & " of the month."

            Case 5 
                strMonth = MonthName(objPattern.MonthOfYear)
                Wscript.Echo "Occurs each year on " & strMonth & " " & _
                    objPattern.DayofMonth & "."                 
            Case 6 
                intInstance = objPattern.Instance
                Select Case intInstance
                    Case 1
                        strInstance = "first"
                    Case 2
                        strInstance = "second"
                    Case 3
                        strInstance = "third"
                    Case 4
                        strInstance = "fourth"
                    Case 5
                        strInstance = "fifth"
                    Case 6
                        strInstance = "sixth"
                End Select
                Select Case objPattern.DayOfWeekMask
                    Case 1
                        strDay = "Sunday"
                    Case 2
                        strDay = "Monday"
                    Case 4
                        strDay = "Tuesday"
                    Case 8
                        strDay = "Wednesday"
                    Case 16
                        strDay = "Thursday"
                    Case 32
                        strDay = "Friday"
                    Case 64
                        strDay = "Saturday"
                End Select
                strMonth = MonthName(objPattern.MonthOfYear)
                Wscript.Echo "Occurs on the " & strInstance & " " & strDay & " of " & _
                    strMonth & " each year."
        End Select
        Wscript.Echo "Start time: " & objPattern.StartTime
        Wscript.Echo "Start date: " & objPattern.PatternStartDate
        Wscript.Echo "End date: " & objPattern.PatternEndDate
        Wscript.Echo
    End If
Next


顯示: