Submitted By: Kent Finkle

Sorts all the worksheets in an Excel spreadsheet by name.

PowerShell
Edit|Remove
$comments = @'Script name: Sort-Worksheets.ps1
  Created on: Saturday, May 05, 2007
  Author: Kent Finkle
  Purpose: How can I use Windows Powershell to Sort Worksheets in a Workbook?
  '@    

#-----------------------------------------------------  
function Release-Ref ($ref) 
{
  ([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
  [System.__ComObject]$ref) -gt 0)
  [System.GC]::Collect()  [System.GC]::WaitForPendingFinalizers()
}  
#-----------------------------------------------------    

$arrNames = @()
    $objExcel = new-object -comobject excel.application
    $objExcel.Visible = $True
    $objWorkbook = $objExcel.Workbooks.Open("C:\Scripts\Inventory.xls")   
 
foreach ($objWorksheet in $objWorkbook.Sheets) {
    $arrNames += $objWorksheet.Name}
    $arrNames = $arrNames | Sort
    for ($i = $arrNames.Count - 1; $i -gt 0; $i--) {
      $objSheet1 = $objWorkbook.Sheets.Item($arrNames[$i])
      $objSheet2 = $objWorkbook.Sheets.Item($arrNames[$i-1])
           $objSheet2.Move($objSheet1)  }    

$a = Release-Ref($objSheet2)  
$a = Release-Ref($objSheet1)  
$a = Release-Ref($objWorksheet)  
$a = Release-Ref($objWorkbook)  
$a = Release-Ref($objExcel)