Einbindung von Funktion in Queries.Add
05.02.2021 21:25:55
Funktion
zur Zeit versuche ich ein Makro zu schreiben, das nahezu identische Queries in einem Loop ausführt. Der einzige Unterschied zwischen den Queries ist, dass sie jeweils auf eine andere Spalte Bezug nehmen sollen. Die erste Query auf Column1, die zweite auf Column2 usw...
Deshalb habe ich an den beiden kritischen Stellen, also da wo bei der ersten Query Column1 stünde, die function GetColumnAct(x)eingefügt, quasi als Variablen-Ersatz.
Hier ist der Code:
Sub ColumnLoop()
For x = 1 To 5
ActiveWorkbook.Queries.Add Name:=GetColumnAct(x), Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Excel.Workbook(File.Contents(""C:\Users\felix\OneDrive\Dokumente\GIZ Consultancy\Technisch\Teststruktur\FV_MA_Abfragen\Aktuelle Abfrage NEU\MA_Daten_AktuelleAbfrageNEU.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & " Tabelle1_Sheet = Quelle{[Item=""Tabelle1"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Höher gestufte Header"" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]" & _
")," & Chr(13) & "" & Chr(10) & " #""Spalte nach Trennzeichen teilen"" = Table.SplitColumn(#""Höher gestufte Header"", ""Mitarbeiter (eMail) TN-Projekt"", Splitter.SplitTextByDelimiter("","", QuoteStyle.Csv), {""MA.1"", ""MA.2"", ""MA.3""})," & Chr(13) & "" & Chr(10) & " #""Zusammengeführte Abfragen"" = Table.NestedJoin(#""Spalte nach Trennzeichen teilen"", {""TN Projekt""}, Tabelle1, {""TN Projekt""}, ""Tabelle1"", JoinKind.FullOuter)," & Chr(13) & "" & Chr(10) & " #""Erweiterte Tabelle1"" = Table.ExpandTableColumn(#""Zusammengeführte Abfragen"", ""Tabelle1"", {""TN Projekt"", ""Fachverbunds Nr."", ""TN Bezeichnung"", ""Fachverbundsbezeichnung"", ""MA.1"", ""MA.2"", ""MA.3""}, {""Tabelle1.TN Projekt"", ""Tabelle1.Fachverbunds Nr."", ""Tabelle1.TN Bezeichnung"", ""Tabelle1.Fachverbundsbezeichnung"", ""Tabelle1.MA.1"", ""Tabelle1.MA.2"", ""Tabelle1.MA.3""})," & Chr(13) & "" & Chr(10) & " #""Entfernte Spalten"" = Table.RemoveColumns(#""Erweiterte Tabelle1"",{""Tabelle1.TN Projekt"", ""Tabelle1.Fachverbunds Nr."", ""Tabelle1.TN Bezeichnung"", ""Tabelle1.Fachverbundsbezeichnung""})," & Chr(13) & "" & Chr(10) & " #""Tiefer gestufte Header"" = Table.Demote" & _
"Headers(#""Entfernte Spalten"")," & Chr(13) & "" & Chr(10) & " #""Transponierte Tabelle"" = Table.Transpose(#""Tiefer gestufte Header"")," & Chr(13) & "" & Chr(10) & " ColumnNext = #""Transponierte Tabelle"" [GetColumnAct(x)]," & Chr(13) & "" & Chr(10) & " #""In Tabelle konvertiert"" = Table.FromList(ColumnNext, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & Chr(13) & "" & Chr(10) & " #""Entfernte Duplikate"" = Table.Distinct(#""In Tabelle konvertiert"")," & Chr(13) & "" & Chr(10) & " #""" & _
"Entfernte leere Zeilen"" = Table.SelectRows(#""Entfernte Duplikate"", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"""", null})))," & Chr(13) & "" & Chr(10) & " #""Transponierte Tabelle1"" = Table.Transpose(#""Entfernte leere Zeilen"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Transponierte Tabelle1"""
Next x
End Sub
Und hier der Code der Function:
Public Function GetColumnAct(ColNumber) As String
Dim ColumnAct As String
ColumnAct = "Column" & ColNumber
GetColumnAct = ColumnAct
End Function
Das Problem ist, dass diese Vorgehensweise an der ersten Stelle klappt, bei Name:=GetColumnAct(x) aber danach, innerhalb des formula:= .... Arguments nicht mehr.
Weiß jemand woran das liegt? Verwende ich die falsche Syntax um die function in die Formula einzubauen?
Für jede Hilfe bin ich sehr dankbar!
Herzliche Grüße
Felix