Disable loading Power Query sources after opening an Excel spreadsheet (source doesn't exist error)

Question

I load a local Json file using Power Query in Excel. The source file is dynamic (the source file is picked up via button using FileDialog and then updated via VBA in the query formula). The query looks something like this:

let
    Quote = Json.Document(File.Contents("C:\last_loaded_file.json")),
                                         ^^^^^^^^^^^^^^^^^^^^^^^^ this is updated via VBA
    orderItems = Quote[orderItems],
    #"Converted to Table" = Table.FromList(orderItems, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #...,
    #...,
    #"Sorted Rows" = ...
in
    #"Sorted Rows"

The problem is that the last used file is saved in the query. Once I open the Excel and the local source file doesn't exist (e.g. on a different computer), the Excel will show up with an error.

[DataSource.Error] Could not find file 'C:\last_loaded_file.json'.

[DataSource.Error] Could not find a part of the path 'C:\folder\folder\...'.

Is there a way how to tell Excel NOT TO refresh power queries when opening Excel (because the source file doesn't exist)? The actual connection has all relevant checkboxes to refresh disabled. However, Excel keeps trying to refresh the power query (loading data from the unavailable Json file).

I also tried to change the query itself (via Power Query Advanced Editor) to avoid loading but I didn't find a way how avoid loading a non-existing file.

Any hints appreciated, thanks.

CraZ · Accepted Answer · 2022-09-15 15:47:51Z

1

Well, the problem is Excel templates (*.xlst, *.xlmt). When you save a spreadsheet as a template, Excel asks:

This workbook contains external data. Do you want Microsoft Excel to clear the data before saving the template, and then automatically refresh the data whenever the template is opened?

Yes No Cancel

And the last user who saved the file as template hit the Yes button. This has caused the troubles with auto-refreshing the power query from non-existing file when opening the template.

Solution: Hit the No button when saving the template.

answered Sep 15, 2022 at 15:47

CraZ

1,85417 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Disable loading Power Query sources after opening an Excel spreadsheet (source doesn't exist error)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related