Odświeżanie zapytań PowerQuery za pomocą VBA #22

W tym wpisie chcemy napisać kod VBA, który odświeża wszystkie zapytania albo tylko pojedyncze zapytanie PowerQuery. Odświeżanie wszystkich zapytań jest prostsze, ponieważ możesz nagrać ten kod sam i później podpiąć go do przycisku.
Najpierw musimy włączyć rejestrowanie makr — polecenie Zarejestruj makro z karty Deweloper.

W oknie, które się otworzy 

nadajesz nazwę makru (np.: Refresh), ewentualnie podpinasz skrót klawiszowy. To trochę bez sensu ponieważ polecenie odśwież wszystko z karty Dane (rys. 3) ma już przypisany do siebie skrót klawiszowy (Ctrl + Alt + F5). Upewniasz się, że makro zostanie zapisane do tego skoroszytu i naciskasz klawisz OK. Teraz ważne, żebyś nie klikał nigdzie w komórki arkusza, tylko od razu przeszedł do karty Dane i kliknął polecenie odśwież wszystko.

Teraz możesz już wyłączyć rejestrację makra – polecenie Zatrzymaj rejestrowanie z karty Deweloper.

Teraz nawet bez zaglądania do kodu makra możesz wstawić dowolny kształt lub obraz i kliknąć na niego prawym przyciskiem myszy, by z podręcznego menu wybrać opcję przypisz makro.

Następnie z listy dostępnych makr wybierasz to, które zarejestrowałeś.

I już możesz odświeżać wszystkie zapytania PowerQuery (oraz tabele przestawne i inne połączenia) za pomocą kliknięcia w ‘przycisk’ (kształt/obraz).

Cały kod makra wygląda tak:

Sub Refresh()
ActiveWorkbook.RefreshAll
End Sub

Jeśli chciałbyś odświeżać tylko pojedyncze zapytanie to trudniejsze zadanie i rejestrator makr Ci raczej w tym nie pomoże (patrz film), za to możesz skorzystać z kodu poniżej.

Sub Makro1()
ActiveWorkbook.Connections("Zapytanie — tProdukty_k").Refresh
End Sub

Ważne, że do nazwy zapytania dodajesz prefiks "Zapytanie — " (w innych język ten prefiks jest inny), a i myślnik nie jest standardowy, więc najlepiej, żebyś skopiować cały kod VBA (w Excelu ten myślnik ma KOD = 151).

Na koniec jeszcze pętla (jaką możesz znaleźć na różnych portalach w internecie), która pomoże Ci odświeżyć wszystkie zapytania PowerQuery w pliku (zmieniłem tylko prefiks na polski):

Sub Makro1()

Dim con As WorkbookConnection
Dim Cname As String

For Each con In ActiveWorkbook.Connections
If Left(con.Name, 12) = "Zapytanie — " Then
Cname = con.Name
With ActiveWorkbook.Connections(Cname).OLEDBConnection
.BackgroundQuery = False 'or true, up to you
.Refresh
End With
End If
Next

End Sub

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP