How to protect Excel using PowerShell?

ImportExcel - PowerShell module

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.

Last month we have been hiding things in Excel, so this week we are going to make sure they are protected as well. Excel offers multiple levels of password protection and its options:

  • locking file with a password, i.e. without key phrase opening file is not possible
  • protecting workbook’s structure
  • lastly, protecting individual worksheets from a handful of operations

Preparation

Let’s 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 = "ImportExcelHowTo009.xlsx"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

As an example, we will use some dummy data. It is irrelevant, we just need something other than empty cells.

# create some dummy data
$data = ConvertFrom-Csv -InputObject @"
Phone,Dept
111-111-1111,James Mary
222-222-2222,Patricia John
333-333-3333,Jennifer Robert
444-444-5555,Michael Linda
555-555-5555,Elizabeth William
"@

In the next step, we are going to ask for a password and save it as a variable, so we do not have passwords in plain text in the scripts.

# prompt for password and save it to a variable for later
$password = Read-Host -AsSecureString "Please enter your password"
$password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))

Protecting Excel file with PowerShell

In this section we are going to create a new Excel workbook using Export-Excel with -Password parameter and the password saved in the previous paragraph.

# create Excel workbook protected by password
$data | Export-Excel -WorksheetName HowToProtect -Path $excelFiles -Password $password

Next, if we try to open it with either Import-Excel or Open-ExcelPackage without providing the password we are going to get this error:

# importing protected Excel file without password fails
Import-Excel -Path $excelFiles

<#
The file is not an valid Package file. If the file is encrypted, please supply the password in the constructor.
#>

# opening protected Excel package without password fails
Open-ExcelPackage -Path $excelFiles

<#
Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password"
#>

In order to fix this, we need to use -Password parameter.

# for protected Excel files use -Password parameter
Import-Excel -Path $excelFiles -Password $password
$excelPackage = Open-ExcelPackage -Path $excelFiles -Password $password
<#
Phone        Dept             
-----        ----             
111-111-1111 James Mary       
222-222-2222 Patricia John    
333-333-3333 Jennifer Robert  
444-444-5555 Michael Linda    
555-555-5555 Elizabeth William
#>

Additionally, we can also set a password to the existing file. In order to do so, let’s get rid of the previous file and recreate it – this time without a password.

# drop the existing file
Remove-Item $excelFiles -ErrorAction SilentlyContinue

# create the file without password protection
$data | Export-Excel -WorksheetName HowToProtect -Path $excelFiles

The above will allow us to open the Excel package without password, then without making any changes we can close/save it with the password using Close-ExcelPackage with -Password parameter.

# open Excel file with PowerShell
$excelPackage = Open-ExcelPackage -Path $excelFiles

# save with password protection and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Password $password
Password protected Excel workbook

Protecting Excel worksheets with PowerShell

The Microsoft Excel allows to put a password on single worksheets as well.

# open password protected Excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles -Password $password

# select the worksheet to protect
$excel = $excelPackage.Workbook.Worksheets['HowToProtect']

# protect the worksheet and select what is allowed or blocked when the worksheet protection is on
Set-WorksheetProtection -Worksheet $excel -IsProtected -BlockSelectUnlockedCells

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

Since we are protecting all the unlocked cells (by default all of them are locked) we cannot select any. Additionally, any operation will cause the warning below.

In addition to that under Review > Protect we can see the option to Unprotect Sheet. At this point, if the workbook is not password protected, clicking the button would allow us to make the changes. Otherwise, it will use the workbook’s password.

As long as we do not use the main password, we need to set the worksheet protection one. See the example below with a little hack as the function does not support setting passwords at the moment.

# clean up the existing file
Remove-Item $excelFiles -ErrorAction SilentlyContinue

# create workbook without password
$data | Export-Excel -WorksheetName HowToProtect -Path $excelFiles

# open Excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles

# select the worksheet to protect
$excel = $excelPackage.Workbook.Worksheets['HowToProtect']

# set the worksheet password
$excel.Protection.SetPassword('DoNotPutPasswordsInTheCode')

# save and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Show 
Worksheet password prompt

Protecting Excel workbooks with PowerShell

Although it would make sense to put this section before the worksheets, there is no dedicated function to support that.

Similarly to last bit with the password for worksheets we are going to dig deeper into workbook properties.

# open Excel package
$excelPackage = Open-ExcelPackage -Path $excelFiles

# set the worksheet password
$excelPackage.Workbook.Protection.SetPassword('life is like a box of chocolates')

# lock the structure
$excelPackage.Workbook.Protection.LockStructure = $true

# save and open the file
Close-ExcelPackage -ExcelPackage $excelPackage -Show
Workbook password prompt

At first, it looks like nothing happened as the button Protect Workbook did not change like the one for Sheet. However, when clicked we see the prompt that says Unprotect Workbook.

Summary

Summing up, it is a great feature of MS Excel to allow protecting files, workbook even specific worksheets. Even more, ImportExcel supports that and all these operations can be done in PowerShell.

Thank you,
Mikey

Leave a Reply

%d bloggers like this: