Select most recent of multiple files, then import to one Excel sheet

Question

I'm having trouble finding a way to select the most recent version of several files in the same folder and importing them into Excel. For example:

Files in the folder: Spanish.csv Spanish(1).csv Spanish(2).csv English.csv English(1).csv French.csv (There are many more languages and files here, but I'm including only these for simplicity)

From that folder, I want to select these files: Spanish(2).csv English(1).csv French.csv

and import them into one existing worksheet.

So far I have:

Sub GetFiles()

    Dim MyPath As String
    Dim Spanish As String
    Dim English As String
    Dim French As String
    Dim LanguageFiles(2) As String

    MyPath = "C:\example\"

    'Make sure that the path ends in a backslash
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    Spanish = Dir(MyPath & "Spanish*.csv")
    English = Dir(MyPath & "English*.csv")
    French = Dir(MyPath & "French*.csv")

    I WANT TO SOMEHOW GET THE MOST RECENT VERSION OF EACH AND PASS IT TO THE LANGUAGEFILES ARRAY AND IMPORT IT TO A SINGLE WORKSHEET.

    LanguageFiles(0) = Spanish
    LanguageFiles(1) = English
    LanguageFiles(2) = French

For i = LBound(LanguageFiles) To UBound(LanguageFiles)
         With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & LanguageFiles(i), Destination:=Range("A" & Rows.Count).End(xlUp).Offset(1, 0))
            .Name = "Sample"
            .FieldNames = False
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    Next i
End Sub

This code doesn't actually work because I'm cobbling several pieces together, and I don't know if I'm even on the right track. Can someone help me out please?

Look at using the filesystemobject, and the datelastmodifed, and you can use like in the search, so if fl.name like " * Spanish * " then compare the date.... — Nathan_Sav, Mar 22 '16 at 18:27
http://stackoverflow.com/questions/9205137/using-vbscript-to-find-most-recent-file-date-in-a-single-folder — Nathan_Sav, Mar 22 '16 at 18:28
http://www.4guysfromrolla.com/webtech/faq/FileSystemObject/faq5.shtml — Nathan_Sav, Mar 22 '16 at 18:29
@Nathan_Sav - those are great places to start, but bear in mind they may fail if someone modifies an older version of a file for whatever reason. — Scott Holtzman, Mar 22 '16 at 18:34
wouklnt that be the latest version then? or are you thinking the (x).csv is acting as ver number? — Nathan_Sav, Mar 22 '16 at 18:35
What happens if the spanish(6).csv is renamed to spanish(1).csv ? :) — Nathan_Sav, Mar 22 '16 at 19:08
The files are downloaded statistics from a website, so they are never edited and resaved, and they always have the same name every time they are downloaded. Since Windows appends a "(1)" to a filename when it's dragged into a folder where an identically named file already exists, I need to make sure that my Excel file always imports the most recent download. Checking for either datelastmodified or a number on the end of the filename would likely work. — Kimber Warden, Mar 22 '16 at 21:11

Scott Holtzman · Answer 1 · 2016-03-23T13:11:29.847

Place the below loop inside the area where you wrote:

I WANT TO SOMEHOW GET THE MOST RECENT VERSION OF EACH AND PASS IT TO THE LANGUAGEFILES

I just wrote the loop for the Spanish files, but you can easily add another loop to capture the French, English, etc. file and adjust the variable names to load into the LanguageFiles array.

Do While Spanish <> ""

        If InStr(1, Spanish, "(") Then 'test to see if there is a number

            Dim bVersion As Boolean
            bVersion = True 'set this variable to true for later

            'extract which version it is - will work for any digit version number
            Dim iVersionTest As Integer, iVersion As Integer
            iVersionTest = CInt(Mid(Spanish, InStr(1, Spanish, "(") + 1, InStr(1, Spanish, ")") - InStr(1, Spanish, "(") - 1)) 
            'for another method see
            'iVersionTest = CInt(Split(Split(Spanish,"(")(1),")")(0)

            'is current version greater than what is already stored? if so, make it latest version
            Dim sLatestVersion 
            If iVersionTest > iVersion Then
                sLatestVersion = Spanish
                iVersion = iVersionTest
            End If

        Else

            'if there's no other version make the lone file the latest version
            If Not bVersion Then sLatestVersion = Spanish

        End If

        Spanish = Dir

Loop

LanguageFiles(0) = sLatestVersion

Ok :) I've recently started using something like this to extract the number from within brackets in a string Split(Split(strInput, "(")(1), ")")(0) little less to track than mid's. — Nathan_Sav, Mar 22 '16 at 19:01
Thanks, @Scott Holtzman. I did as you suggested and am getting the following error at Spanish = Dir. "Invalid procedure call or argument" — Kimber Warden, Mar 22 '16 at 20:26
@KimberWarden - that is strange. Code worked for me when I tested. Also, it's a pretty standard line (see [here](http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba)) — Scott Holtzman, Mar 23 '16 at 13:08

Select most recent of multiple files, then import to one Excel sheet

1 Answers1