CODEWITHSUNDEEP
excelvbapowerquery
PYJTER
PYJTER

Reputation: 91

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

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

Upvotes: 0

Views: 784

Answers (1)

ceci
ceci

Reputation: 774

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

Upvotes: 1

Related Questions