嗨，Scripting Guy！

  • 發行項

嗨，Scripting Guy！

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

還有，別忘了瞧瞧全新經過改良的嗨，Scripting Guy！過往文件。

今天的問題：要如何將活頁簿中的工作表排序？

要如何將活頁簿中的工作表排序？

問

嗨，Scripting Guy！要如何將活頁簿中的工作表排序？

-- FS
答

FS，您好。要如何將活頁簿中的工作表排序呢？其實替活頁簿中的工作表排序沒有想像中的簡單，但並不表示不可能，只是稍嫌複雜。所以待會如果有任何看不懂的地方，不要氣餒，就連我們也不是完全瞭解。最後只要指令碼執行結果沒問題就可以了，不是嗎？

首先假設有個活頁簿，裡面有一組像這樣的工作表。

Microsoft Excel


要怎麼將這些工作表依字母順序排序呢？方法如下：

 
On Error Resume Next
Dim arrNames()
intSize = 0
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Inventory.xls")
For Each objWorksheet in objWorkbook.Sheets
    ReDim Preserve arrNames(intSize)
    arrNames(intSize) = objWorksheet.Name
    intSize = intSize + 1
Next
For i = (UBound(arrNames) - 1) to 0 Step -1
    For j= 0 to i
        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
            strHolder = arrNames(j+1)
            arrNames(j+1) = arrNames(j)
            arrNames(j) = strHolder
        End If
    Next
Next 
For i = UBound(arrNames) to 1 Step -1
    Set objSheet1 = objWorkbook.Sheets(arrNames(i))
    Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))
    objSheet2.Move objSheet1
Next

我們很瞭解您目前的心情，我們會盡量解釋清楚。

這個指令碼一開始其實很簡單，首先建立一個名為 arrNames() 的動態陣列。為什麼？雖然 Excel 內建許多功能，還是缺了一項：將工作表排序的指令。由於 Excel 並未內建將工作表排序的方法，因此要自己動手。我們採取的方式是擷取所有工作表的名稱並在動態陣列內排序，接著才將陣列內的項目排序。一旦陣列經過排序，即可取得所有工作表的正確順序，接著再使用 Excel 的 Move 方法，把每一個工作表移到想要的位置。

陣列建立完畢後，我們就有一個標準的現成指令碼，可以用來建立 Excel.Application 物件的執行個體，將 Visible 屬性設定為 True，然後利用 Open 方法開啟 C:\Scripts\Inventory.xls 檔案。現在是挽起袖子上工的時候了。

第一個工作是擷取所有工作表的名稱然後放在動態陣列中。這就是下面這行程式碼所要做的：

For Each objWorksheet in objWorkbook.Sheets
    ReDim Preserve arrNames(intSize)
    arrNames(intSize) = objWorksheet.Name
    intSize = intSize + 1
Next

方才設定了 For Each 迴圈，逐步處理 Sheets 集合，望文生義，這個集合來自活頁簿裡所有的工作表。再針對集合中的每一個工作表使用 ReDim Preserve 命令，調整動態陣列的大小。陣列開始的大小是 0，表示陣列裡只有一個項目 (陣列的大小總是等於項目數目減 1)。我們是從何得知要將大小設為 0 呢？因為我們已將把名為 intSize 的計數器變數 (也就是一開始在指令碼設定的變數 0) 指派給這個陣列。

接著把集合中第一個工作表的名稱指派給陣列中的第一個項目，也就是下面的指令碼：

arrNames(intSize) = objWorksheet.Name

將計數器變數遞增 1，然後執行迴圈，對集合中第二個工作表重複同一個程序，這個工作表的名稱會變成陣列中的第二個項目。全部完成後就會得到一個陣列，陣列裡的項目會依以下順序呈現：

Sheet2
c
a
Sheet1
b

目前為止還不賴。下一個部分就比較難了，這裡要使用一個「簡單」的反昇排序 (Bubble Sort)，依字母順序將陣列中的項目排序：

For i = (UBound(arrNames) - 1) to 0 Step -1
    For j= 0 to i
        If UCase(arrNames(j)) > UCase(arrNames(j+1)) Then
            strHolder = arrNames(j+1)
            arrNames(j+1) = arrNames(j)
            arrNames(j) = strHolder
        End If
    Next
Next

今天的專欄不會對反昇排序的處理方式深入說明，如果您有興趣進一步瞭解它運作的原理，不妨收聽 Scripting Week 2 網路廣播 Things the Scripting Guys Never Told You。簡單地說，反昇排序的原理是將陣列內的每一個項目與陣列中的另一個項目相比，如果有必要則互換位置。舉例來說，陣列中的前兩個項目分別為：

Sheet2
c

依字母順序，c 應該在 Sheet2 的前面，所以反昇排序會把這兩個名字互換，所以它們會成為陣列內的前兩個項目：

c
Sheet2

這個處理會一直重複，直到項目間彼此已經做過比較。結果呢？陣列依字母順序排序好了。

a
b
c
Sheet1
Sheet2

很湊巧，這也是我們希望試算表排序的方式。現在知道哪個試算表排在前面，哪個排在第二個，接著使用下面的程式碼區塊將活頁簿中的試算表排序。

For i = UBound(arrNames) to 1 Step -1
    Set objSheet1 = objWorkbook.Sheets(arrNames(i))
    Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))
    objSheet2.Move objSheet1
Next

上面的指令碼以陣列末為起點 (UBound 可以連到陣列裡的最後一個項目，本例中為 Sheet2)，然後一路往下推 (也就是 Step -1 的用途)。由於陣列已經排序過了，我們知道陣列最後一個項目 Sheet2 也應該是活頁簿的最後一個工作表，也知道最後一個項目往前推一個就是 Sheet1 項目，這也應該是活頁簿倒數第二個工作表。所以我們使用下面這個指令碼，建立 Sheet2 的物件參考：

Set objSheet1 = objWorkbook.Sheets(arrNames(i))

接著使用下面這個指令碼，建立 Sheet1 的物件參考：

Set objSheet2 = objWorkbook.Sheets(arrNames(i-1))

最後呼叫 Move 方法把 Sheet1 搬到 Sheet2 的前面：

objSheet2.Move objSheet1

我們怎麼知道要把 Sheet1 搬到 Sheet2 的前面呢？其實很簡單：因為 Move 方法後面沒有逗號：

objSheet2.Move objSheet1

如果要將 Sheet1 搬到 Sheet2 的後面，得在 Move 方法後面加逗號。

objSheet2.Move, objSheet1

雖然這不是最直覺的處理方法，但的確有效。

指令碼完全執行完畢後，會得到一個看起來像這樣的活頁簿：

Microsoft Excel


如果您還有不懂的地方，別擔心。有時候信心無敵，不用問怎麼做或為什麼。

如需詳細資訊

查看嗨，Scripting Guy！- 過往文件

 

回到頁首 回到頁首