Sort Excel Workbook Sheets by Last Word Using Powershell

PowerShell Help
1

Good Morning Everyone! I have a tough problem. I have a workbook that has many worksheets (896). Each sheet is named “First Name(space)Last Name”. I would like to alphabetize the sheets A-Z using the last name. I have the below code which sorts them by first name. Thoughts on how I can use the second word to sort the sheets, rather than the first word? Any help is greatly appreciated!

# ===== Alphabetize Sheets ======

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$Excel = New-Object -comobject Excel.Application

$Excel.Visible = $true

$workbook = $Excel.Workbooks.Open("$dir\Lochend Timesheet Reports for Period.xlsx")
$worksheetNames = New-Object System.Collections.ArrayList
foreach ($worksheet in $workbook.Worksheets) {
	$worksheetNames.Add($worksheet.Name)
}
$worksheetNames.Sort()
for ($worksheetNamesIndex = 0; $worksheetNamesIndex -lt $worksheetNames.Count - 1; $worksheetNamesIndex++) {
	$worksheetToMoveName = $worksheetNames[$worksheetNamesIndex]
	$worksheetToMove = $workbook.Worksheets.Item($worksheetToMoveName)
	$worksheet1After = $workbook.Worksheets.Item($worksheetNamesIndex + 1)
	$worksheetToMove.Move($worksheet1After)
}


$Excel.ActiveWorkbook.SaveAs($dir + "\Lochend Timesheet Reports for Period $startRange to $endRange.xlsx", $xlFixedFormat)

$Excel.Workbooks.Close()
$Excel.Quit()
2

So it’s a little tricky because you still need to keep a reference to the original worksheet names, otherwise you can’t move them. This is my solution:

# ===== Alphabetize Sheets ======

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlFixedFormat = [Microsoft.Office.Interop.Excel.XlFileFormat]::xlWorkbookDefault
$Excel = New-Object -comobject Excel.Application

$Excel.Visible = $true

$workbook = $Excel.Workbooks.Open("E:\Temp\Book1.xlsx")
$worksheetNames = New-Object System.Collections.ArrayList
foreach ($worksheet in $workbook.Worksheets) {
	$worksheetNames.Add($worksheet.Name)
}

$sortedList = foreach ($worksheet in $worksheetNames) {
    [PSCustomObject] @{
        reverseName   = ($worksheet -split ' ')[1,0] -join ' '
        worksheetName = $worksheet
    }
}

$sortedList = $sortedList | Sort-Object reverseName

$position = 0

foreach ($worksheet in $sortedList) {
    $worksheetToMoveName = $worksheet.worksheetName
    $worksheetToMove = $workbook.Worksheets.Item($worksheetToMoveName)
    $worksheet1After = $workbook.Worksheets.Item($position + 1)
    $worksheetToMove.Move($worksheet1After)
    $position++
}

$Excel.ActiveWorkbook.SaveAs('E:\Temp\Book1.xlsx')

$Excel.Workbooks.Close()
$Excel.Quit()