Log In
Register
Home
Active Member
Xtreme Pivot Tables
Power BI
May 11, 2020
Offline
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!
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Anke,
If file A is open, querying File_A data from another file will return a query error. Maybe you can change the code to save a copy of the File A and query the copy instead.
Can you upload a sample query?
Active Member
Xtreme Pivot Tables
Power BI
May 11, 2020
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
Xtreme Pivot Tables
Power BI
May 11, 2020
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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
Offline
Hello,
If you don’t already have, do a file copy of file A as a backup just in case.
Open file A and save a new copy using Save As. Rename file A and then rename the copy so it gets the same name as file A had. Does the error message still pop up?
Br,
Anders
Active Member
Xtreme Pivot Tables
Power BI
May 11, 2020
Offline
Hello Anders,
thank you for your reply. Yes, we had done that however it did not resolve the issue.
Active Member
Xtreme Pivot Tables
Power BI
May 11, 2020
Offline
Thank you Catalin, I opted for starting the queries from a folder. I replicated my queries and with that linked my pivot tables to the new data source; this seems to do the trick. All testing was successful, it will go life next week. Thank you for your help.
Active Member
December 3, 2022
Offline
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
As said, filtering out temp file works only outside OneDrive.
Active Member
December 3, 2022
Offline
Thanks..
I was trying to find a way to refresh a query, where the source excel file is open and resides on OneDrive.
I could not find a solution..so is it safe to say... Files on OneDrive must be closed in order to refresh the query.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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)
