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
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
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
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
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.
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.