4 Solutions For Excel File Is Locked Error In Power Automate

4 Solutions For Excel File Is Locked Error In Power Automate

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





And supply this key-value pair to the headers.

Preferbypass-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']




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.






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

Subscribe
Notify of
guest

13 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Bob
Bob
1 month ago

What a timely article! I’ve been working on several flows that grab an attachment from email, save to OneDrive, and then create a table. I get getting “locked” issues and realized that I need to give time between saving to OD before editing the file. So I set all the flows to wait between 15 minutes. However, I never considered the looping through until the file is unlocked. Genius. Thank you again for a great and resourceful article!

Pablo
Pablo
1 month ago

Hey Matthew. Nice article. There is a Solution #5 tho: if you are (hopefully) working on a solution, you can create a child flow that performs whatever you want to do with the Excel file. The child flow will keep kicking retries until the lock is gone. Of course your flow will take longer to fully run.
Thanks for #2 and bypassing the lock, had no idea!

Last edited 1 month ago by Pablo
CWes
CWes
18 days ago
Reply to  Pablo

The child will also generate a failed flow instance over and over until it succeeds. I guess you could minimize the retries to cut down on those dreaded pink lines.

George Winters
George Winters
1 month ago

Terrific !!!

Debra Swain
Debra Swain
1 month ago

I assume I could adapt this for a Word document in a Sharepoint library, too? I have a flow that is updating a metadata field and I get frequent notifications that this step in the flow has failed because the Word document is locked for editing.

RealSlimShady
RealSlimShady
24 days ago

In solution #4, if I have the file already open, the action “Update file properties: Status in progress” will fail

Larisa
Larisa
3 days ago

Thanks a lot, it was killing me.