Verify your account to enable IT peers to see that you are a professional.
mace
Go to the file server and open Computer Management/Shared Folders/Open Files. Sort it by file name and find the name of the file and see if it's locked. I usually see that happen when a file didn't get closed properly, and the server thinks whoever had it open at that time still has it open. Right click, "Close open file."
If this doesn't work then save as a new file. Then delete the old version and rename the new file to the old name to avoid loosing data.
If the end user is on a laptop. opened the file. took the computer offsite [like say home] came back with the file open and continued to work in it. then tried to save it this is a very common issue. It gets out of sync with the file server and won't let you overwrite the file even if it isn't in use
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?
Anke Baumann
Active Member
Members
Xtreme Pivot Tables
Power BI
Forum Posts: 5
Member Since: May 11, 2020
3
November 2, 2022 - 10:53 pm
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
Catalin Bombea
Iasi, Romania
Member
Dashboards
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
Forum Posts: 1903
Member Since: November 8, 2013
4
November 4, 2022 - 9:53 pm
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
Anke Baumann
Active Member
Members
Xtreme Pivot Tables
Power BI
Forum Posts: 5
Member Since: May 11, 2020
5
November 16, 2022 - 1:23 am
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.
Catalin Bombea
Iasi, Romania
Member
Dashboards
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
Forum Posts: 1903
Member Since: November 8, 2013
6
November 16, 2022 - 6:03 am
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.
Anders Sehlstedt
Eskilstuna, Sweden
VIP
Members
Forum Posts: 894
Member Since: December 7, 2016
7
November 16, 2022 - 9:08 am
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
Anke Baumann
Active Member
Members
Xtreme Pivot Tables
Power BI
Forum Posts: 5
Member Since: May 11, 2020
8
November 30, 2022 - 11:12 pm
Hello Anders,
thank you for your reply. Yes, we had done that however it did not resolve the issue.
Anke Baumann
Active Member
Members
Xtreme Pivot Tables
Power BI
Forum Posts: 5
Member Since: May 11, 2020
9
November 30, 2022 - 11:21 pm
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.
Cagnam
North Amarica
Active Member
Members
Forum Posts: 3
Member Since: December 3, 2022
10
December 3, 2022 - 4:03 am
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
Catalin Bombea
Iasi, Romania
Member
Dashboards
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
Forum Posts: 1903
Member Since: November 8, 2013
11
December 3, 2022 - 6:19 pm
As said, filtering out temp file works only outside OneDrive.
Cagnam
North Amarica
Active Member
Members
Forum Posts: 3
Member Since: December 3, 2022
12
December 4, 2022 - 4:27 am
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.
Catalin Bombea
Iasi, Romania
Member
Dashboards
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
Forum Posts: 1903
Member Since: November 8, 2013
13
December 4, 2022 - 4:23 pm
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.