共用方式為


嗨,Scripting Guy!

嗨,Scripting Guy!

歡迎使用 TechNet 專欄,Microsoft Scripting Guys 會在此為您解答有關系統管理指令碼的常見問題。您有關於系統管理指令碼方面的問題嗎?請將電子郵件傳送到 scripter@microsoft.com。我們無法保證能夠逐一回答每個問題,不過我們會盡力而為。

還有,別忘了瞧瞧全新經過改良的嗨,Scripting Guy!過往文件

今天的問題:如何根據日期來變更儲存格的背景色彩?


如何根據日期來變更儲存格的背景色彩?

嗨,Scripting Guy!我有個定期使用指令碼進行分析的 Microsoft Excel 試算表,我要如何將儲存格的色彩在該儲存格中存放的資料過期後改為紅色?

-- DM

DM,您好。我們今天原本是想要對紅色字母的日期下一些機智和詼諧的評語,可是實在連一個都想不出來。所以,我們要假裝沒有時間想這些無聊的東西,直接切入正題。

附註:這篇專欄是由不久前發現自己想不出以字母 K 為開頭的字的同一名 Scripting Guy 寫的 (對啦,有 kite、kitten、kid、kindergarten,他現在都知道,有上百萬個字都是以字母 K 開頭)。照他思考的速度,這篇專欄答案的地方很快會是一大片空白,整個版面只留問題。

嘿,是誰說這也算是進步的?!


為了把說明重點放在手邊的工作,就假設您有一個非常簡單的試算表,是由一系列資料組成 (全都在 A 欄)。這個簡單的試算表看起來像這樣:

Microsoft Excel


我們要做的是建立一個可以開啟此檔案的指令碼,檢查每個儲存格內的日期,假使有特定日期已經過期的話,就將儲存格的背景色彩改成紅色。換句話說,在指令碼完成之後,我們希望試算表看起來像這樣:

Microsoft Excel


這辦得到嗎?既然 Scripting Guy 的英文已經無可救藥,至少還是有能力偶爾寫寫指令碼:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
x = 1
Do Until objExcel.Cells(x,1).Value = ""
    If CDate(objExcel.Cells(x,1).Value) < Date Then
        objExcel.Cells(x,1).Interior.ColorIndex = 3
    End If
    x = x + 1
Loop

考慮到它所做的事,這指令碼看起來一點也不長,先是建立 Excel.Application 物件的執行個體,接著將 Visible 屬性設為 True。我們之後使用 Open 方法來開啟 C:\Scripts\Test.xls 檔案,所有這些動作所做的是,開啟我們的試算表,使之顯示在螢幕上。

出現試算表之後,我們指派 1 的值給一個名為 x 的計數器變數,用它隨著我們處理整份試算表來追蹤目前的列。接下來呢,就開始處理整份試算表,建立一個迴圈持續執行到在 A 欄中遇到空白的值為止。這正是這行程式碼的用途:

Do Until objExcel.Cells(x,1).Value = ""
注意:為什麼要這麼做?這是為了單純起見,我們假設您在 A 欄中沒有空白儲存格,因此只是一碰到空白儲存格,就知道是到了資料結尾。


在 Do Loop 中,我們會檢查看目前儲存格的值是不是小於今天的日期,採取的方法是,取得目前儲存格的值 (objExcel.Cells.(x,1).Value),並使用 VBScript CDate (character-to-date) 函式確定該值被當作日期對待,接著將該值與目前日期相比較 (使用 Date 函式來完成)。如果儲存格的值小於目前日期,就使用這行程式碼將儲存格的內部 (背景) 色彩變為紅色:

objExcel.Cells(x,1).Interior.ColorIndex = 3
注意:那我們怎麼知道將 ColorIndex 設為 3 會有紅色的背景?答案是,要等到執行此處參考的指令碼後才知道,這會顯示所有的背景色彩及其對應值。

都了解了嗎?要是您不確定為什麼要檢查 objExcel.Cells(x,1) 的值,記住 x 這個變數是建立用來追蹤目前的列,因為 x 是從值 1 開始,第一次執行迴圈是要檢查儲存格 (1,1):列 1,欄 1。完成第一次檢查之後,x 就會增加 1,所以下一次執行迴圈就會檢查儲存格 (2,1):列 2,欄 1 等等。

就這麼簡單、迅速,您可以說這是…嗯…嗯,一時想不出要說什麼,反正您能夠意會就好。


如需詳細資訊

查看嗨,Scripting Guy!- 過往文件

 

回到頁首 回到頁首