


Active Member

May 11, 2020

Hello everyone, since a couple of weeks I am having trouble with the following:
Our master file (File_A) contains our base data, as well as VBA macros.
File_A is open.
Scenario 1:
One File_A macro opens File_B and then updates a Power Query of File_B (querying File_A data).
The File_A macro then saves and closes File_B.
Scenario 2:
A File_C is open as well; File_C contains VBA macros.
One File_C macro opens File_D and updates a Power Query of File_D (querying File_A data).
The File_C macro then saves and closes File_D.
My problem for both scenarios:
After a PQ update, no matter if triggered by a macro from within File_A, or triggered by a macro from the external File_C, it is impossible to save File_A -> I get the error message "Someone else is working in File_A right now. Please try again later".
This has only appeared recently (maybe 4 weeks ago?), it worked fine before.
We are using Microsoft 365.
File A is stored on a shared drive (however it is not a shared file).
File B, C and D are stored in repositories on SharePoint.
Any idea for the cause and what to fix here?
Thanks a lot!

Active Member

May 11, 2020

Thank you Catalin.
Where could I find specification / description of this behaviour (file A is open, querying File_A data from another file will return a query error)?
Is it possible to catch this error in VBA?
What I find strange is that I had no problem with the setup of scenario 1 and 2 until recently.
Thanks again, Anke


November 8, 2013

Hi Anke,
When you query file A, if it is open, the query will fail.
There are 2 ways to avoid this: in power query, if a file is open, the query editor will show 2 entries for the same file, one of them will start with the "~" prefix, it's a temporary hidden file. Filter the Name column to exclude files that begins with "~" symbol. This works only if file A is on a normal folder, not OneDrive/Sharepoint folder. (on these folders, excel creates a different type of hidden temporary copy, with .tmp extension)
The second way is to write code to copy File A into a different location, point the queries to the copy instead of the original file.
You can catch errors in a query:
On Error Resume Next
tbl.QueryTable.Refresh
If Err.Number <> 0 then ' Query failed, you can read Err.Description to see the reason

Active Member

May 11, 2020

Thanks Catalin.
I am not querying a folder, but file A, so the temporary "~"file should not be the issue?
My query in file B is updating without error; however, when trying to save file A after the successful query, file A is displaying the error "Someone else is working in file A right now...". If I close file A and leave file B open on my computer, and then my colleague opens file A on her computer (in my first post I had mentioned it is a file on a shared drive) they can still not save it. Only after I have closed all Excel files on my computer, file A will not throw the error anymore.
File A is our database that is manually updated (and therefore opened) multiple times during the day. We have a couple of Power Queries connected to file A. It would have been great to query file A directly; to create a copy seems counterintuitive, but maybe it's the way to go.


November 8, 2013

I am not querying a folder, but file A, so the temporary "~"file should not be the issue?
Instead of querying file a directly, start a query from folder. The query will list all the files in that folder, apply a filter to identify file A. Apply another filter to remove the temp copy starting with ~. Note that the query will get the changes only after you save file A.

Answers Post

VIP

Trusted Members

December 7, 2016


Active Member

December 3, 2022

There are 2 ways to avoid this: in power query, if a file is open, the query editor will show 2 entries for the same file, one of them will start with the "~" prefix, it's a temporary hidden file. Filter the Name column to exclude files that begins with "~" symbol. This works only if file A is on a normal folder, not OneDrive/Sharepoint folder. (on these folders, excel creates a different type of hidden temporary copy, with .tmp extension)
Re:Above -- How do you filter out the .tmp files.
I can filter "~" ok
but cannot see the .tmp files when i link to onedrive.
Thanks
Cagnam


November 8, 2013

Worth to mention something about OneDrive:
OneDrive is not just on your local computer, those files are in cloud as well.
While you cannot refresh a query to a local file from OneDrive folder, you can still connect to the cloud version with no restriction.
https://www.myonlinetraininghu.....ower-query
If you have a personal account, not OneDrive Business, and the above link does not work, try:
Get from onedrive cloud the share link, but you need to apply a trick:
Initially, the onedrive link looks like:
https://1drv.ms/x/s!AuMAcqo4%5B...]
You have to use it in a browser to open the file, the link will change to something like this:
https://onedrive.live.com/view.aspx?resid=E1C2D[...]3!457&ithint=file%2cxlsx&authkey=!AH9[...]
The final step is to change from view to download:
https://onedrive.live.com/download.aspx?resid=E1C2D[...]3!457&ithint=file%2cxlsx&authkey=!AH9[...]
This final link can be used in a Query from Web
1 Guest(s)
