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:
- What is the error message displayed in Microsoft Windows Based Script Host?
- 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.
- 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. **