4 Solutions For Excel File Is Locked Error In Power Automate

Imported reference from Matthew Devaney's blog for learning purposes. Original: https://www.matthewdevaney.com/4-solutions-for-excel-file-is-locked-error-in-power-automate/. Author: Matthew Devaney.

4 Solutions For Excel File Is Locked Error In Power Automate Posted by - Matthew Devaney on - November 5, 2023 39 Comments

The Power Automate error message file <filename> is locked for shared use by <user email> is caused by attempting to update SharePoint file properties after performing an Excel action. I have encountered this message many times and I will share 4 methods to deal with the locked file error. Table of Contents

Introduction: The Store Inventory Report Automation

Generate The Excel File Locked For Shared Use Error Message

Solution #1 – Loop Until The Excel File Is Unlocked

Solution #2 – Delete The Excel File & Bypass The Shared Use Lock

Solution #3 – Copy Excel File To Temp Folder To Read The Rows

Solution #4 – Write A Filled-In Excel Template To Another Folder

Introduction: The Store Inventory Report Automation

An automation reads the rows of an Excel table and updates the file properties in SharePoint. When the automation is run the following error message appears: the file <filename> is locked for shared use by <user email> .

Generate The Excel File Locked For Shared Use Error Message

The best way to understand why the Excel file is locked for shared use error message occurs and prove the solutions will work is to recreate the error message in a simple flow.

Open SharePoint and make a new document library named File Lock Testing . Then create a new blank Excel file titled Store Inventory List.xlsx .

In the Store Inventory Excel file, create a new table named tblInventory. Setup the table and populate its data as shown in the image below.

Go to Power Automate and create a new instant flow. Add the following actions. Excel – List Rows Present In A Table SharePoint – Update File Properties

Save the flow and then try to run it. The flow run will fail with the error message the file <filename> is locked for shared use by <user email> . This is because the Excel actions locks the file making it impossible for SharePoint to update its file properties.

Solution #1 – Loop Until The Excel File Is Unlocked

An Excel file will become unlocked up to 10 minutes after the file is closed. We can incorporate a loop into the flow to check when the file is unlocked and finish updating the file properties.

When the flow encounters a locked Excel file it will throw an error. Configure the Run After settings for the Condition action to ensure the flow continues to run whether the update file properties action succeeded or failed.

The condition action checks the update file properties outputs for status code 400. Status code 400 means the file was locked for shared use. If the file was locked for shared use the flow will delay for 30 seconds before it goes back to the start of the loop. Otherwise, the loop will end.

Result: Loop Until The Excel File Is Unlocked

When we run the flow we can see the Do Until loop executes its actions 20 times before succeeding. The flow retries the update file properties action several times and leaves the loop once the Excel file becomes unlocked.

Solution #2 – Delete The Excel File & Bypass The Shared Use Lock

If we want to delete an Excel file that is locked there is a simple solution. We can tell SharePoint to ignore the file lock and delete it anyways.

To do this add a SharePoint – Send An HTTP Request action to the flow. Select the Site Address and use the POST method.

Copy and paste this Uri into the flow action.

_api/web/Lists/GetByTitle( 'File Lock Testing' )/GetItemById( 1 )/recycle Code language: JavaScript ( javascript )

And supply this key-value pair to the headers. Prefer bypass-shared-lock

Result: Delete The Excel File & Bypass The Shared Use Lock

When we run the flow action it deletes the Excel file from SharePoint while ignoring any potential files locks.

The Excel file no longer appears in the File Lock Testing document library.

Solution #3 – Copy Excel File To Temp Folder To Read The Rows

There is no need to wait for an Excel file to become unlocked when we only need to read the rows. We can copy the Excel file to a temporary folder, read the rows of the Excel file, and delete it when we are done.

Go to the File Lock Testing SharePoint document library and create a new folder named Temporary Files .

Modify the flow to update the Store Inventory Excel file status to In Progress . Then copy the file to the temporary folder. Read the rows from the Excel file and delete the file when done. Change the final status of the original Excel file to Succeeded .

An expression is required to get the the Excel table identifier.

Use this expression in the Table field of the Excel – List Rows Present In A Table Action .

first(body( 'Filter_array:_Name_eq_tblInventory' ))?[ 'id' ] Code language: JavaScript ( javascript )

Result: Copy Excel File To Temp Folder To Read The Rows

Run the flow to see the result. The flow successfully bypasses the Excel file lock by copying it to a temporary folder and reading the copied file’s rows.

Solution #4 – Write A Filled-In Excel Template To Another Folder

The final example is useful when we want to fill-in an Excel template without locking the file. We can copy the Excel file to a temporary folder to perform updates. Then copy the updated file to an output folder and delete the temporary file.

Add another folder named Output Files to the File Lock Testing document library.

Update the flow we made for Solution #3 to look like this. An Excel – Update A Row action is substituted for the Excel – List Rows Present In A Table Action . And we add an additional SharePoint – Copy File action to move the completed file to the Output Files folder.

Result: Write A Filled-In Excel Template To Another Folder

Give the flow a test run to ensure it works.

The newly updated Excel file is created in the Output Files folder and its status property is changed to Succeeded. Did You Enjoy This Article? 😺

Subscribe to get new Copilot Studio articles sent to your inbox each week for FREE Enter your email address Sign Me Up Questions?

If you have any questions or feedback about 4 Solutions For Excel File Is Locked Error In Power Automate please leave a message in the comments section below. You can post using your email address and are not required to create an account to join the discussion. Matthew Devaney Power Automate

Save Docusign Document To SharePoint With Power Automate