Asked by:
Excel Power Query refresh issue when input file is open

Question
-
Hi dear helpers,
I have a few excel files with tables that i want to consollidate into one "overview" file. I solved this realtively simply by using Power Query. My only problem is that I dont seem to be able to refresh the overview file while input files are open.
I googled this issue, and found that a possible solution could be to exclude files containing ~$, a procedure i guess could adequatly solve this problem. But i painfully discovered that this solutions may not aply to me, as I have these files store in Onedrive?
So to be clear: Is there any way to refresh a query while the input files, saved in onedrive, are open?
All replies
-
The source data files should be referenced in the Overview file as read-only - if they are configured as Data Sources. I routinely use CSV's as Data Sources in Excel Dashboards, and the CSV's are written & modified independently of the dashboards, without issue.
Although the Overview file will not update externally, i.e. when the source data files are opened, and/or modified, the Overview's data sources can be set to refresh when the file is opened. The data sources can also be configured to do a background refresh on a defined interval.
Alternatively, you could script a refresh of the Overview file. I found this on StackOverflow: https://stackoverflow.com/questions/47159268/powershell-script-open-excel-update-external-data-save-as. I haven't tested this. I imagine it could cause problems by locking the Overview workbook, or when attempting to modify the Overview workbook when it is open & locked.
-
-
-
Hi Harald. What error(s) do you get when trying to refresh your queries when the source files are open?
Ehren
- Proposed as answer by Ehren - MSFTMicrosoft employee, Owner Tuesday, November 19, 2019 6:13 PM
-