Unable to password protect excel file.

[jogden]

For the last year+ I have a flow that uses the UI automation to click file > info > protect workbook > encrypt with password. It has always worked unless I interrupted it. Until today, now it cannot find the elements and is throwing an error. My attempts to refresh the elements or use a menu/list under form filing also did not work.

is there another way to accomplish this? Has anyone else ran into similar issues lately?

I have another issue in a web automation where before it would run in the background allowing me to work on other things now it errors out if I try to do anything else on the system. 

It seems the FLOWS are getting less automation friendly lately.  

[jogden]

I did find a work around using the send keys TAB ENTER ARROW DOWN ENTER from the file > info tab. Its a dirty solution but it worked.

[Nived_Nambiar]

Hi @jogden 

Below Powershell script will help you to lock an excel file with password

# Define the path to the Excel file
$filePath = "%excel_file%"

# Define the password you want to use
$password = "%password%"

# Create an Excel object
$excel = New-Object -ComObject Excel.Application

# Open the Excel file
$workbook = $excel.Workbooks.Open($filePath)

# Set the password to protect the workbook
$workbook.Password = $password

# Save and close the workbook
$workbook.Save()
$workbook.Close()

# Quit Excel
$excel.Quit()

# Release the Excel object from memory
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

here i have passed two variables from power automate desktop , one is %excel_file% which stores the excel file path second is %password% which stores the password by which we need to protect the excel file, you can create both variables with values 

I have created a demo using the above powershell script like above 🙂

Kindly like and mark the answer as solution if it help you 🙂

[kinuasa]

Hi,
It would be easy to set the password using the 'Run VBScript' action as shown below.

GetObject("C:\Test\Excel\SampleBook.xlsx").Password = "pass"

Regards,
kinuasa

*Below is a sample code. Please copy and paste to use.

SET PassWord TO $'''pass'''
SET WorkbookFilePath TO $'''C:\\Test\\Excel\\SampleBook.xlsx'''
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: WorkbookFilePath Visible: True ReadOnly: False Instance=> ExcelInstance
Scripting.RunVBScript VBScriptCode: $'''GetObject(\"%WorkbookFilePath%\").Password = \"%PassWord%\"''' ScriptOutput=> VBScriptOutput
Excel.CloseExcel.CloseAndSave Instance: ExcelInstance

[Nived_Nambiar]

Thank you @kinuasa  for sharing this 🙂

[jogden]

Can these be executed before saving the file? This FLOW creates the file populates the report password protects and then saves the file to a shared location. I understand the VBA script can be ran after it is saved but can it be done before?  

[Nived_Nambiar · Accepted solution]

Hi @jogden 

First of all sorry for late response.

You can try this powershell script

# Get the Excel Application object for the running instance
$excel = [System.Runtime.InteropServices.Marshal]::GetActiveObject("Excel.Application")

# Get the active workbook
$workbook = $excel.ActiveWorkbook

# Set the password for the workbook
$password = ConvertTo-SecureString "%pwd%" -AsPlainText -Force
$workbook.Password = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($password))

This powershell script take the active excel and set password to it. here %pwd% is the variable from powerautomate which stores the password by which we need to lock excel file.

Sample flow

Note: Before running make sure that there is no other excel files are running in the machine , otherwise it may not work

Hope it helps

Mark it as solution if it resolves your query

[jogden]

Sorry I got sidetracked on a different project. Just came back to this today. This worked thanks!