none
Excel Power Query refresh issue when input file is open RRS feed

  • 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? 

    Friday, October 25, 2019 6:38 PM

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.

    Saturday, October 26, 2019 6:34 PM
  • Hi PremiumSource, and thank you for helping. 

    Could you please explain in detail how i can set source data as read-only? 


    Monday, October 28, 2019 1:59 PM
  • No problem. If the connections to external CSV's and other data sources are configured as data connections in Excel, they should already be opening as read only.
    Monday, October 28, 2019 11:43 PM
  • Hi Harald. What error(s) do you get when trying to refresh your queries when the source files are open?

    Ehren

    Thursday, October 31, 2019 7:13 PM
    Owner
  • When opening the source files in another program, such as Excel, hold down the Shift key, right-click, and choose Open Read Only. This will prevent the files from locking.
    Monday, November 4, 2019 2:05 AM