I’ve spent hours researching this with no luck. If I am pulling data from a table within an Excel Workbook over to another Excel Workbook using Power BI, can I keep the source file open while refreshing? I always receive the data source error, file used by another program. I guess I have to close the workbook and then refresh? I’d like to keep both open as I enter data and refresh.
No, the source workbook must be closed for Power Query to refresh, sorry.
Not sure about that. I disabled OneDrive and was able to achieve what I was asking. I can refresh the query while the source is open. Thus, OneDrive is the culprit (my guess with the syncing in the background).
You didn't mention OneDrive initially. If you're referencing an Excel file on your hard drive it must be closed for refreshing. As for OneDrive, I haven't tested it enough to say. Did the changes get picked up by the refresh or is it still showing the previously sync'd data?
Do not have to close the source file, just save it. Once saved, the refresh picks up all changes immediately in the other file and both files remain open at the same time.
Ah, that'll be because the OneDrive sync client is uploading the changes from your PC to the OneDrive file. So, why were you getting the error initially? Was the Excel file only saved on your hard drive?
Correct, that's what I was able to figure out. I am pretty certain I was getting the original error because of the OneDrive sync client (i.e. the error was not be able to access the Data Source file because it was in use). Once I "paused" OneDrive, everything worked as it should.
Also, of note, there is a way to have this work and have OneDrive act as "normal." You can achieve this by having the Query point to the URL link of the OneDrive source file (vs. having the Query point directly to the Excel Workbook). However, if you are like me and live in an area where internet speeds are not good, the delay is too long to refresh. Doing above, cuts down the refresh time tremendously.
Hi
I have been having the same issue using Excel Power Query and found a solution.
If you edit your query and remove queries from files beginning with "~$" ( equals open files ), then at least it works for me.
Erik
I am posting along this thread as it has to do with a refresh issue with a file not associated with OneDrive (i.e. the source data file is on the local drive but no sync with OneDrive; call this file "test.") and not working properly. Every time I refresh the main query, the "test" file cannot be resaved to the local drive; it has a "file sharing" message. If I close close the main query file, the source file works fine and can be saved. It's almost as if every refresh of the query is keeping that source file from being saved, generating the "file sharing" message. Any thoughts? I had no luck with Erik's solution above. Thanks.
ExcelNovice, you haven't said what type of file 'test' is. If it's an Excel file then it must be closed for Power Query to refresh properly, so this may be the cause of your problem.
Sorry, I made the mistake of "assuming" since talking about the same power query refreshing with another open file but good point. The source file is an Excel .xlsm file.
What is odd is that I've been able to make this work before with the source file open (and, does not matter, source file is local drive or OneDrive). For some reason, Excel is giving me the "file sharing" message within the source file while trying to save it after a refresh from the main file. I've been able to create new "test" files and it's working but I'd like to know how to fix going forward. I've tried all the basic stuff (indexing, sharing wizard checked, virus disabled, etc.).
Not sure, sorry. As a general rule I try to keep my source files closed when refreshing.
