Office Space:撰寫 Microsoft Office 應用程式指令碼的秘訣
歡迎蒞臨 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 迴圈,逐一執行篩選過的項目集合;每一個會議我們都要回報 Subject、Duration 和 Location。接著就得到如下所示的資料:
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