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?

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

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

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.

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.

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

Hello Anders,

thank you for your reply. Yes, we had done that however it did not resolve the issue.

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.

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

As said, filtering out temp file works only outside OneDrive.

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.

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.myonlinetraininghub.com/get-data-from-onedrive-or-sharepoint-with-power-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 [...]

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