Jak za pomocą VBA zmienić wartość parametru dla zapytań w Power Query

Korzystając z Power Query dość często korzystam z Parametrów. Jest to bardzo wygodna rzecz gdyż np. źródłem danych nie jest na sztywno wpisana nazwa pliku tylko parametr, który przechowuje ową nazwę.

Oczywiście kilka różnych zapytań może korzystać z tego samego parametru co jest bardzo wygodne, bo gdy zmieni się nazwa pliku wejściowego to edytuję tylko jeden parametr a nie wszystkie zapytania po kolei.

Niedawno natrafiłem na pewien problem: otóż napisałem proste makro, które prosi użytkownika o wskazanie pliku. I właśnie nazwę tego pliku wraz z ścieżką chciałem zapisać jako wartość parametru.

Aby zmienić wartość parametru o nazwie Plik należy napisać taki oto kod:

ThisWorkbook.Queries("Plik").Formula = "wartość"

Niestety po napisaniu kawałka kodu:

ThisWorkbook.Queries("Plik").Formula = "c:\raporty\maj.csv"

Wyskoczył błąd

Utworzyłem nowy parametr, następnie napisałem taki oto fragment kodu:

Debug.Print ThisWorkbook.Queries("Plik").Formula

I moim oczom ukazał się taki oto ciąg znaków:

"c:\raporty\maj.csv" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]

Czyli widzimy, że przekazując dane do właściwości Formula musimy podać nie tylko treść tych danych, ale też parę dodatkowych informacji (np. że typ danych to Tekst). Tak więc tak wygląda prosta funkcja zmieniająca wartość parametru (tekstowego)

Sub ZmienParametr(NazwaParametru As String, NazwaPliku As String)
    Dim strPar As String
    strPar = Chr$(34) 'cudzysłów
    strPar = strPar + NazwaPliku
    strPar = strPar + Chr$(34)
    strPar = strPar + " meta [IsParameterQuery=true, Type="
    strPar = strPar + Chr$(34)
    strPar = strPar + "Text"
    strPar = strPar + Chr$(34)
    strPar = strPar + ", IsParameterQueryRequired=true]"
    
    ThisWorkbook.Queries(NazwaParametru).Formula = strPar
End Sub

Sub TestZmianaParametru()
    ZmienParametr "Plik", "c:\raporty.csv"
End Sub

Oto przykładowe wartości dla właściwości Formula dla różnych typów danych:

Dowolny (liczba)

6 meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Dowolny (tekst)

"aaaa" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

Liczba dziesiętna (zwróć uwagę że używamy kropki a nie przecinka)

3.5 meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true]

Data/Godzina

#datetime(2023, 1, 1, 12, 34, 56) meta [IsParameterQuery=true, Type="DateTime", IsParameterQueryRequired=true]

Data

#date(2023, 1, 1) meta [IsParameterQuery=true, Type="Date", IsParameterQueryRequired=true]

Godzina

#time(12, 34, 56) meta [IsParameterQuery=true, Type="Time", IsParameterQueryRequired=true]

Data/Godzina/Strefa czasowa

#datetimezone(2023, 5, 28, 12, 34, 56, 2, 0) meta [IsParameterQuery=true, Type="DateTimeZone", IsParameterQueryRequired=true]

Czas Trwania

#duration(0, 12, 34, 56) meta [IsParameterQuery=true, Type="Duration", IsParameterQueryRequired=true]

Wartość logiczna

true meta [IsParameterQuery=true, Type="Logical", IsParameterQueryRequired=true]

Tekst

"c:\raport.csv" meta [IsParameterQuery=true, Type="Text", IsParameterQueryRequired=true]