Hi,

I have a problem with a query in my workbook, which updates fine when prompted manually by the user, but which fails when being updated from VBA.

The query is constructed using Power Query to pull the data from a named range "Raw_data" (which expands/contracts dynamically to always match the no. of columns/rows in the dataset) in sheet 1, and looks something like this:

let
    Source = Excel.CurrentWorkbook(),
    Raw_data = Source{[Name="Raw_data"]}[Content],
    #"Promoted Headers1" = Table.PromoteHeaders(Raw_data),
etc.......
When updated manually from the Power Query pane / data tab, the query refreshes just fine. However, when updated through VBA with the code below, I get the error "Rune-time error '1004': We couldn't refresh the connection 'Query - Test". Here's the error message we got: [Expression.Error] The key didn't match any rows in the table."

ActiveWorkbook.Connections("Query - Test").Refresh
I am using a similar code for all other queries which work with no problem at all (although they pull data from actual tables rather than named ranges). Previously, the query in question was linked to a table rather than a named range, with the VBA working fine. However, going forward, the query needs to pull data from the named range.

Does anyone have any suggestions on how to solve the issue?

Thanks!