Automatischer Datenabruf mit Variabel
04.04.2022 21:42:55
Abdelali
ich habe ein kleines Problem. Ich habe über den Macrorecorder eine Datei per Datenabruf augezeichnet. Jetzt möchte ich, das die Quelle Variabel wird. Also das was ich in der Tabelle RAW in Zelle A26 als Pfad eintrage, über diesen Pfad soll der Abruf starten. Leider bekomme ich immer eine Fehlermeldung. Mache ich irgendwas falsch?
das ist mein Code
Sub Fullreport()
Dim pfad As String
pfad = AtiveWorkbooks.Worksheets("RAW").Range("A26").Value
ActiveWorkbook.Queries.Add Name:="Monat", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Excel.Workbook(File.Contents(Pfad), null, true)," & Chr(13) & "" & Chr(10) & " Monat_Sheet = Quelle{[Item=""Monat"",Kind=""Sheet""]}[Data]," & Chr(13) & "" & Chr(10) & " #""Höher gestufte Header"" = Table.PromoteHeaders(Monat_Sheet, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"",{{""Monat"", Int6" & _
"4.Type}, {""employee_ID"", Int64.Type}, {""Vorname"", type text}, {""Nachname"", type text}, {""Team"", type text}, {""KSG"", type text}, {""DK_Vol_gen"", type number}, {""DK_Stk_gen"", Int64.Type}, {""Kreditvol_fin"", type number}, {""Kredit_Stk_fin"", Int64.Type}, {""Rang_DK"", Int64.Type}, {""Ertrag"", type number}, {""FrablFaktor"", type number}, {""Cashie_Vol""" & _
", type number}, {""Rang_Cashie"", Int64.Type}, {""TDMA_Vol"", Int64.Type}, {""RSV_Stk_fin"", Int64.Type}, {""RSV_Quote"", type number}, {""RSV_Aftersale"", Int64.Type}, {""Rang_RSV_NV"", Int64.Type}, {""Gelb_Stk"", type any}, {""Gelb_Vol"", Int64.Type}, {""WSSB_Leads"", Int64.Type}, {""Rang_WSSB"", Int64.Type}, {""VSUP_Stk"", Int64.Type}, {""MSF_Stk"", Int64.Type}, " & _
"{""RSV_Vol_fin"", Int64.Type}, {""RAng_RSV_Vol"", Int64.Type}, {""TL_Email"", type text}, {""Leader_sID"", type text}, {""avg_Ticket_Fin"", type number}, {""avg_Ticket_Gen"", type number}, {""avg_Ertrag"", type number}, {""InhouseVolume"", type number}, {""Finquote"", type number}})," & Chr(13) & "" & Chr(10) & " #""Gefilterte Zeilen"" = Table.SelectRows(#""Geänderter Typ"", each [Monat] >=" & _
" 202201)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Gefilterte Zeilen"""
Worksheets("RAW FULL").Activate
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Monat;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Monat]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Monat"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
Vielen Dank