Can't open Excel file in SparePoint Online library

Question

I am having a periodic issue with 2 Excel files in my SharePoint Online document library. Let me preface this by saying that these 2 files are connected to a SharePoint list and are updated/refreshed using vbscript that is run every hour using Task Scheduler (not at the same time). About every 2 weeks one of the files (either one) will fail to update. An error is thrown in the Microsoft Windows Based Script Host. Without fail when this happens I cannot even open the Excel file (in app) in the SharePoint library. So in other words, I cannot even manually refresh the worksheets in the file. The error I get then is an Excel error that says the file is open in another application. This seems to indicate that there is still something open in the background somewhere but I can't find it.

My usual haphazard approach is to use Task Manager to delete any instances of Excel that it shows. I also use Credential Manager to delete any credentials related to Office 365. I also delete my password from the Edge browser. I also kill the scheduled Task in Task Scheduler. I then log out and back into the SharePoint site, cross my fingers and try to open it. I might also log off my computer and back in. Sometimes I'm successful after doing all this multiple times. Other times I try for days to get it running again.

I'm hoping someone might have some ideas on what I can look for.

Answer 1

I have tested with Task Scheduler automatically updating Excel data connection using vbscript referring to this article: Script to Update Excel Chart in SharePoint. Excel client prompts that the file is locked by “me” when opening the file.

Please confirm as following:

1. What is the error message displayed in Microsoft Windows Based Script Host?
2. Do you have trouble opening the Excel file in Excel Online (in browser)? Per my test, “Refresh Data Connection” is supported in Excel Online, and it does not throw error mentioned.
3. Try temporarily disable the task via Task Schedule and see if the Excel file opens successfully.

*Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link. *

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
**Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. **

Answer 2

It sounds like the lock is being established and not released properly. This is often due to how the client accesses the file and doesn't close it properly. Without knowing your VBScript code, it would be tough to troubleshoot this however I would recommend looking at more modern frameworks to perform this task.

Answer 3

I figured out a series of steps that will fix my issue but I really need to figure out how to prevent it from happening. I need my script to continue to run hourly when I go on vacation.

1. Sign out of SharePoint Online site
2. Using Credential Manager, remove all MS-related credentials
3. Using Task Manager, end task on any instances of Excel that display (not sure if this is necessary)
4. Reboot
5. Log in to SharePoint site
6. Now able to open Excel file
7. Now able to run vbscript in Windows Script Host
8. Presumably Task Scheduler will run run the script hourly until it fails again.