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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |

Answers

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |