How to move Excel worksheets using PowerShell?

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

Do you have a long list of the Excel sheets in one file? How do you find what you need quickly? Moving around spreadsheets and making coloured tabs might help you.

Moving worksheets around

When we want to place the worksheet in a different place it is simple as drag&drop or using the context menu.

The tabs can be moved to the end or start of the list, as well as put after or before selected sheet.

Preparation

In this paragraph, we are going to prepare our environment now to make sure the file does not exist.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo007.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

Next, we will create a workbook with multiple worksheets. The data is irrelevant in this case as we are focusing on worksheets. We will add worksheets in a loop.

# workbook with multiple sheets
for ($i = 1 ; $i -le 5 ; $i++){
    $i | Export-Excel -WorksheetName HowToSheet$i -Path $excelFiles -MoveToStart
    }

Moving worksheets with PowerShell

In this part, we will work on an existing workbook that already has worksheets. If you want to learn how to add new worksheets using the Add-Worksheet have a look at this blog post.

In case you haven’t noticed in the script above we used -MoveToStart switch, that means all the new worksheets were added at the beginning. The list looks like that now:
HowToSheeet5
HowToSheeet4
HowToSheeet3
HowToSheeet2
HowToSheeet1

That’s one way to place the worksheets but it, since we are piping data in it, is not ideal for the existing worksheets as it will overwrite them.

However, when we use the method below we can “save” the worksheet in a new location without damaging the data.

# move existing worksheet to the start/end without overwriting the data
Export-Excel -WorksheetName HowToSheet1 -Path $excelFiles -MoveToStart
Export-Excel -WorksheetName HowToSheet5 -Path $excelFiles -MoveToEnd

As a result, the list looks like below:
HowToSheeet1
HowToSheeet4
HowToSheeet3
HowToSheeet2
HowToSheeet5

After that, let’s shuffle some worksheets again:

# move existing worksheet after/before other worksheets without overwriting the data
Export-Excel -WorksheetName HowToSheet2 -Path $excelFiles -MoveAfter HowToSheet1
Export-Excel -WorksheetName HowToSheet4 -Path $excelFiles -MoveBefore HowToSheet2

The result will be:
HowToSheeet1
HowToSheeet4
HowToSheeet2
HowToSheeet3
HowToSheeet5

Finally, what if we want to sort the tabs by the name? For that, we are going to use Get-ExcelSheetInfo to get the names of the worksheets.

# sort tabs by name in ascending order
$sheets = Get-ExcelSheetInfo -Path $excelFiles | Sort-Object Name
foreach ($sheet in $sheets.Name) {
    Export-Excel -WorksheetName $sheet -Path $excelFiles -MoveToEnd
}

That’s how it is going to look now:
HowToSheeet1
HowToSheeet2
HowToSheeet3
HowToSheeet4
HowToSheeet5

And of course, after we are done playing, we can revert to the original order:

# sort tabs by name in descending order
$sheets = Get-ExcelSheetInfo -Path $excelFiles | Sort-Object Name
foreach ($sheet in $sheets.Name) {
    Export-Excel -WorksheetName $sheet -Path $excelFiles -MoveToEnd
}

Bonus: changing tab colours

In addition to the main topic, I want to mention about one more thing. Recently there was a question about changing the tab colour on modules GitHub repo. For example, within Excel, we do it by right-clicking on a tab and selecting Tab Color. The module itself does not have any function to do that, but we can hack a little bit by changing the TabColor property directly.

For instance, any color from the list or use RGB values.

# open the excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles -KillExcel

# get the worksheets
$excel = $excelPackage.Workbook.Worksheets

# set colors to each worksheet tab
$excel['HowToSheet1'].TabColor = 'Green'
$excel['HowToSheet2'].TabColor = 'Yellow'
$excel['HowToSheet3'].TabColor = 'Red'
$excel['HowToSheet4'].TabColor = 'Blue'
$excel['HowToSheet5'].TabColor = [System.Drawing.Color]::FromArgb(201,255,172)

# save changes and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Show

Summary

In conclusion, this week’s exercise was to move spreadsheets around and add them some colours.

Thank you,
Mikey