How to change only source and refresh of a power query using VBA?

Question

I have this question I am trying to update the connection (source) in PowerQuery. Can you please help me how it should be done in VBA ??

MY DebugPrint (Sorry, but I have a Polish version of excel365;)):

TEST_CHANGE let
    Źródło = Excel.Workbook(File.Contents("C:\Users\revol\Desktop\PQ_11.xlsm"), null, true),
    TEST_CHANGE_Sheet = Źródło{[Item="TEST_CHANGE",Kind="Sheet"]}[Data],
    #"Nagłówki o podwyższonym poziomie" = Table.PromoteHeaders(TEST_CHANGE_Sheet, [PromoteAllScalars=true]),
    #"Zmieniono typ" = Table.TransformColumnTypes(#"Nagłówki o podwyższonym poziomie",{{"Column1", type text}, {"Column2", type text}, {Column3", Int64.Type}, {"Column4", type text}, {"Fiscal year / period", type text}, {"Some else", type number}}) in
    #"Zmieniono typ"
-----------------------;

I tried to use this instruction but it doesn't work; (

ActiveWorkbook.Queries.Item("MyQuery").Formula = "[Excel.Workbook(File.Contents("C:\Users\revol\Desktop\change_source.xlsm"), null, true)]"

thank you in advance for your help

ceci · Accepted Answer · 2021-04-30 09:13:26Z

1

This function will refresh all powerquery connections:

Sub Refresh_All_Data_Connections()
Dim objConnection, bBackground
    For Each objConnection In ThisWorkbook.Connections
        'Get current background-refresh value
        bBackground = objConnection.OLEDBConnection.BackgroundQuery

        'Temporarily disable background-refresh
        objConnection.OLEDBConnection.BackgroundQuery = False

        'Refresh this connection
        objConnection.Refresh

        'Set background-refresh value back to original value
        objConnection.OLEDBConnection.BackgroundQuery = bBackground
    Next

End Sub

answered Apr 30, 2021 at 9:13

ceci

7841 gold badge5 silver badges15 bronze badges

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

2 Comments

PYJTER Over a year ago

I just wanted to change the source to a different one and refresh it. I have source "C:\Users\revol\Desktop\PQ_11.xlsm" and i have change it for "C:\Users\revol\Desktop\change_source.xlsm"

horseyride Over a year ago

see stackoverflow.com/questions/67301739/… for similar solution

Collectives™ on Stack Overflow

How to change only source and refresh of a power query using VBA?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related