AW: Mehrere CSV-Dateien automatisch einlesen
22.11.2019 15:02:31
Gerri
Hallo Günther,
wie es scheint ist der "Power Query-Editor" seit 2013+ das "Daten abrufen". Damit habe ich bisher ja auch meine Abfrage gemacht und dann mit dem Makroeditor alles aufgenommen - eine andere Möglichkeit habe ich dafür auch erst mal nicht gefunden.
An sich hab ich es jetzt sogar zum laufen gekriegt, nur ist der Code halt absolut schrecklich, da der Code intern mit festen Dateinamen (für die Excel-Datei) und internen Tabellennamen arbeitet. Dadurch ist das ganze ziemlich fehleranfällig - Datei darf nie umbenannt werden, wenn man speichert werden durch die Duplikat entfernung auch Zellen gelöscht und ein erneutes Laden funktioniert nicht ohne weiteres und unter "Abfragen und Verbindungen" sind wie erwähnt bereits die internen Tabellennamen in verwendung, ein erneutes nutzen des Skriptes zum laden ist dadurch nicht möglich (crash aufgrund der gleichen Namen).
Vielleicht kann mir ja jemand helfen den Anfang des Skriptes ein wenig "schöner" zu machen, ich hab nur Kleinigkeiten angepasst gekriegt, da mir das ansonsten zu hoch ist.
Sub DatenLadenV5()
' DatenLadenV5 Makro
Range("A1").Select
ActiveWorkbook.Queries.Add Name:="Beispieldatei aus csv transformieren", _
Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Csv.Document(Beispieldateiparameter1,[ _
Delimiter="";"", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Quelle"
ActiveWorkbook.Queries.Add Name:="Beispieldateiparameter1", Formula:= _
"Beispieldatei meta [IsParameterQuery=true, BinaryIdentifier=Beispieldatei, Type="" _
Binary"", IsParameterQueryRequired=true]"
ActiveWorkbook.Queries.Add Name:="Beispieldatei", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Folder.Files(""C:\Users\[hier mein Pfad]" _
")," & Chr(13) & "" & Chr(10) & " Navigation1 = Quelle{0}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Navigation1"
ActiveWorkbook.Queries.Add Name:="Datei aus csv transformieren", Formula _
:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = (Beispieldateiparameter1) => let" & Chr( _
13) & "" & Chr(10) & " Quelle = Csv.Document(Beispieldateiparameter1,[Delimiter="";"", Columns=3, Encoding=1252, QuoteStyle=QuoteStyle.None])" & Chr(13) & "" & Chr(10) & " in" & Chr(13) & "" & Chr(10) & " Quelle" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Quelle"
ActiveWorkbook.Queries.Add Name:="csv", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Folder.Files(""C:\Users\[hier mein Pfad]" _
")," & Chr(13) & "" & Chr(10) & " #""Gefilterte ausgeblendete Dateien1"" = Table.SelectRows(Quelle, each [Attributes]?[Hidden]? true)," & Chr(13) & "" & Chr(10) & " #""Benutzerdefinierte Funktion aufrufen1"" = Table.AddColumn(#""Gefilterte ausgeblendete Dateien1"", ""Datei aus csv transformieren"", each #""Datei aus csv transformieren""([Con" & _
"tent]))," & Chr(13) & "" & Chr(10) & " #""Umbenannte Spalten1"" = Table. _
RenameColumns(#""Benutzerdefinierte Funktion aufrufen1"", {""Name"", ""Source.Name""})," & Chr(13) & "" & Chr(10) & " #""Andere entfernte Spalten1"" = Table.SelectColumns(#""Umbenannte Spalten1"", {""Source.Name"", ""Datei aus csv transformieren""})," & Chr(13) & "" & Chr(10) & " #""Erweiterte Tabellenspalte1"" = Table.ExpandTableColumn(#""Andere entfernte Spalten1""" & _
", ""Datei aus csv transformieren"", Table.ColumnNames(#""Datei aus csv _
transformieren""(Beispieldatei)))," & Chr(13) & "" & Chr(10) & " #""Geänderter Typ"" = Table.TransformColumnTypes(#""Erweiterte Tabellenspalte1"",{{""Source.Name"", type text}, {""Column1"", type text}, {""Column2"", Int64.Type}, {""Column3"", Int64.Type}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Geänderter Typ"""
Workbooks("Vorlage7.xlsm").Connections.Add2 _
"Abfrage - Beispieldatei aus csv transformieren", _
"Verbindung mit der Abfrage 'Beispieldatei aus csv transformieren' in der _
Arbeitsmappe." _
, Array( _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="" _
Beispieldatei aus csv transformieren"";Extended Properties=""" _
, """"), "SELECT * FROM [Beispieldatei aus csv transformieren]", 2
Workbooks("Vorlage7.xlsm").Connections.Add2 "Abfrage - Beispieldateiparameter1" _
, "Verbindung mit der Abfrage 'Beispieldateiparameter1' in der Arbeitsmappe.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location= _
Beispieldateiparameter1;Extended Properties=""""" _
, "SELECT * FROM [Beispieldateiparameter1]", 2
Workbooks("Vorlage7.xlsm").Connections.Add2 "Abfrage - Beispieldatei", _
"Verbindung mit der Abfrage 'Beispieldatei' in der Arbeitsmappe.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Beispieldatei; _
Extended Properties=""""" _
, "SELECT * FROM [Beispieldatei]", 2
Workbooks("Vorlage7.xlsm").Connections.Add2 _
"Abfrage - Datei aus csv transformieren", _
"Verbindung mit der Abfrage 'Datei aus csv transformieren' in der Arbeitsmappe." _
, _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Datei aus _
csv transformieren"";Extended Properties=""""" _
, "SELECT * FROM [Datei aus csv transformieren]", 2
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=csv;Extended _
Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [csv]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "csv"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.Worksheets("Tabelle2").ListObjects("csv").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Tabelle2").ListObjects("csv").Sort.SortFields.Add2 _
Key:=Range("csv[[#All],[Column1]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Tabelle2").ListObjects("csv").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub