Importing newest *.csv file into excel

[DeekMan]

Good afternoon,

I have this code from a recorded macro, that imports the specific file into the open worksheet. (highlighted path and file)

Sub importnewfile()

ActiveWorkbook.Queries.Add Name:="Derby_Station", _
        Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""[COLOR=#ff0000]C:\test\CSV\Derby_Station_Today_20190401024130.csv[/COLOR]""),[Delimiter="","", Columns=16, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Change Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", type " & _
        "text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}, {""Column10"", type text}, {""Column11"", type text}, {""Column12"", type text}, {""Column13"", type text}, {""Column14"", type text}, {""Column15"", type text}, {""Column16"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"" = Table.Skip(" & _
        "#""Change Type"",4)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Removed Top Rows"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Derby_Station"";Extended Properties=""" _
        , """"), Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array( _
        "SELECT * FROM [Derby_Station]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Derby_Station"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = "Derby_Station"

end sub

I would like to import the newest *.csv file from the specified folder, (C:\test\CSV\) rather than the specific file, is this possible please?

any help is appricated

DeeK

 

[welshgasman]

From this link
https://www.tek-tips.com/viewthread.cfm?qid=1565475

I found that this at least does not complain

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=Range("$A$1"))

HTH

 

[DeekMan]

Good Morning Welshgasman,

I am using excel 2019, this incoroprates Power Query, which I only found out by accident and a lot of searching. I found this you tube tutorial, which allows you to refresh the query, with the latest file. This acutally answers my question.

Many thanks for your help in trying to resolve my issue.

best regards

DeeKman.

https://youtu.be/8H9xM741hBQ

 

[welshgasman]

Wow, pretty neat :D

Thanks for the update.
I only have 2007. so if i wanted to do the same, I'd have to persevere, which is the reason I tried to help. :D