VBA can't update query but can be done manually

[Phil123456789]

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:

Please Login or Register  to view this content.

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."

Please Login or Register  to view this content.

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!

[alansidman]

I use the following and it works perfectly for me.

Please Login or Register  to view this content.

[Phil123456789]

Thanks alansidman,

Unfortunately, my workbook contains a bunch of queries, so updating all at once is not a viable option.

Beside, the ActiveWorkbook.RefreshAll unfortunately does not work either in my workbook. Any other suggestions?

I did try to make the named range static rather than dynamic, with the ActiveWorkbook.Connections(“Query - Test”).refresh actually workung. However, I need the dynamic part to ensure that not too many rows and columns are included which drags on performance (on dataset has about 5000 rows and 10 columns, another has 500 rows but 150 columns, etc.)