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

32 Comments
Oldest
Newest
Inline Feedbacks
View all comments
Bob
Bob
1 year 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 year 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 year ago by Pablo
CWes
CWes
1 year 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 year ago

Terrific !!!

Debra Swain
Debra Swain
1 year 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
1 year ago

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

Jorge
Jorge
1 year ago

Hi Matthew – Thanks for the awesome post. I know it’s been mentioned in prior posts about looping through and waiting for a OneDrive file to unlock… Just wondered if you have come across a solution for OneDrive use cases similar to your Sharepoint (HTTP call) solution, where one could possibly make a call to immediately delete a OneDrive file versus looping and waiting until the lock is gone?

Larisa
Larisa
11 months ago

Thanks a lot, it was killing me.

lluis
lluis
11 months ago

What a nice article!!, I have a problem with solution 1, because i don’t know how to define the variable status in the condition, it would help me a lot.
Thanks for the help.

Nate
Nate
9 months ago
Reply to  lluis

Yes! Everything else in this post is spelled out except that step. This isn’t an option from dynamic content.

Kendra
Kendra
11 months ago

Thank you for this article!! I spent a day and a half racking my brain on how to address this issue until finally I found this article. You’re a lifesaver!!

ola
ola
11 months ago

Hi Matt, thanks for this great article but i am stuck in condition status code eq 400. i dont see or cant find that in my list of dynamic contents

Thanks

Nate
Nate
9 months ago

That dynamic content does not exist. I’m using Move file. I don’t have status as an option.

Kim
Kim
8 months ago

Using the outputs(‘Update_file_properties_5’)?[‘body’]?[โ€˜statusโ€™] results in a “The expression is invalid error message.” I’m having the same issue as everyone else in that the Status is not available as dynamic content. Yes, I see that status appears when the flow fails, but that does not help me add Status to the condition. Any other suggestions? Why does status appear as dynamic content for you, but not for others? ๐Ÿ™€

Kim
Kim
8 months ago
Reply to  Kim

Well, suddenly the expression is valid!! Probably some errant character somewhere. Thank you for this solution!!!

Fran
Fran
2 months ago

Hi Matt, these are great options! I have a problem, though. In the Do Until loop, it checks the Update item – similar to yours. However, when the file is still locked, the flow completely fails at the Update item with the ‘locked for shared use’ message. It doesn’t proceed to the condition flow status 400 > delay > start over. Any ideas?

Fran
Fran
2 months ago

Hi Matthew, these are great options! I have a problem, though. In the Do Until loop, it checks the Update item – similar to yours. However, when the file is still locked, the flow completely fails at the Update item with the ‘locked for shared use’ message. It doesn’t proceed to the condition flow status 400 > delay > start over. Any ideas?

Mark Herman
Mark Herman
1 month ago

I’m getting “REFRESH RECOMMENDED A newer version of this file is available on the server” when I open the desktop version (web version is ok). I guess it’s still cached locally. I tried to delete cached items in Excel (Options)

Last edited 1 month ago by Mark Herman
Mark Herman
Mark Herman
1 month ago
Reply to  Mark Herman

If the user clicks the ‘Add shortcut to OneDrive icon in the task bar, a file can be deleted / re-added/re-created, and the user can open in desktop version without the Refresh Recommended error / issue.

Add Shortcut to OneDrive.png
Maarten
Maarten
19 days ago

This doesn’t seem to work when you want to remove a file.