Neuste Datei laden
04.03.2021 16:53:46
Jörn
ich habe mit dem VBA Recorder das Laden und Umwandeln einer CSV aufgenommen. Ich möchte dieses Makro gern so abändern, dass immer die neueste Datei genommen wird.
Es wäre toll, wenn mir jemand helfen könnte.
Gruß Jörn
Sub load()
' load Makro
' Tastenkombination: Strg+v
ActiveWorkbook.Queries.Add Name:="20201018-100547", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Quelle = Csv.Document(File.Contents(""G:\20201018- _
100547.csv""),[Delimiter="","", Columns=25, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & " #""Höher gestufte Header"" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Geänderter Typ"" = Table.TransformColumnTypes(#""Höher gestufte Header"",{{""date"", type text}, {""opponent1"", type tex" & _
"t}, {""opponent2"", type text}, {""videoStart"", type number}, {""comment"", type text} _
, {""uniqueID"", type text}, {""shots__lineCallType"", Int64.Type}, {""shots__id"", Int64.Type}, {""shots__in_out"", Int64.Type}, {""shots__review"", Int64.Type}, {""shots__status"", type text}, {""shots__x"", type number}, {""shots__y"", type number}, {""shots__ballSpeed"", type " & _
"number}, {""shots__impactSpeed"", type number}, {""shots__netHeight"", type number}, {" _
"shots__spin"", type number}, {""shots__playingx"", type number}, {""shots__playingy"", type number}, {""shots__receivingx"", type number}, {""shots__receivingy"", type number}, {""shots__shotType"", type text}, {""shots__timestamp"", type number}, {""shots__playingEmail"", type t" & _
"ext}, {""shots__receivingEmail"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr( _
13) & "" & Chr(10) & " #""Geänderter Typ"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=20201018- _
100547;Extended Properties=""""" _
, Destination:=Range("paste!$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [20201018-100547]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "_20201018_100547"
.Refresh BackgroundQuery:=False
End With
Sheets("Basic Data").Select
Range("B7").Select
End Sub