How to select files in a folder (to get data from), selecting the most updated version based on name of file

I have a code to extract certain data from files in a folder but I would like to select the updated versions of these files (there are ten in total, with newer versions added frequently). The newer versions have higher numbers in the name so for example, the name of one file could be T1_D002 and then T1_D003 would be the new version of the TP1 files in the folder and this would be the case all the way to TP10. I already have a loop going through the folder to select certain files, but it will give all of the versions of the T1-T10's. The T and D parts are not the only characters in the file name. Any help would be wonderful.
Emily BarnesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
The T and D parts are not the only characters in the file name
so can you provide us few real samples and its formats?
0
Fabrice LambertFabrice LambertCommented:
Beside filtering, are file names format enough as criteria ?
What about files creation date and / or files last modified date ?
0
Emily BarnesAuthor Commented:
[sample][/filename]

So an example would be: ABCDEF_123m_TP1_D003_AOB

The ABCDEF won't always necessarily be that number of letters and the same for the numbers and the AOB can be different too but I have already got it sorted to only pull those files with a certain last few characters.

The date last modified won't always necessarily be the latest version in the folder so I can't use a date finding method. The names themselves should be a clear filter.

Thanks
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Bill PrewIT / Software Engineering ConsultantCommented:
It would be good if you shared the existing code that finds the files too, might make it easier for the experts to propose changes or additions that would fit better into that code to find the most recent files.


»bp
0
Fabrice LambertFabrice LambertCommented:
With the informations you provided, I came up with this:
Public Function getFilesFromFolder(ByVal path As String) As Collection
On Error GoTo Error
    Dim FSO As Object       '// Scripting.FileSystemObject
    Dim fld As Object       '// Scripting.Folder
    Dim file As Object      '// Scripting.file
    Dim col As Collection
    Dim rx As Object        '// VBScript_RegExp_55.RegExp
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set fld = FSO.GetFolder(path)
    Set rx = CreateObject("VBScript.Regexp")
    rx.Global = True
    rx.Pattern = "_D\d{3}_"     '// filter criteria
    Set col = New Collection
    For Each file In fld.files
        If (rx.test(file.name)) Then
            col.Add file.name
        End If
    Next
    Set rx = Nothing
    Set fld = Nothing
    Set FSO = Nothing
    Set getFilesFromFolder = col
Exit Function
Error:
    If Not (rx Is Nothing) Then
        Set rx = Nothing
    End If
    If Not (fld Is Nothing) Then
        Set fld = Nothing
    End If
    If Not (FSO Is Nothing) Then
        Set FSO = Nothing
    End If
    err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Function

Public Function getNewestFile(ByRef files As Collection) As String
    Dim item As Variant
    Dim newest As String
    
    newest = files(1)
    For Each item In files
        If (isNewer(item, newest)) Then
            newest = item
        End If
    Next
    getNewestFile = newest
End Function

Private Function isNewer(ByVal lhs As String, ByVal rhs As String) As Boolean
On Error GoTo Error
    Dim rx As Object        '// VBScript_RegExp_55.RegExp
    Dim matchs As Object    '// VBScript_RegExp_55.MatchCollection
    Dim lhsNumber As Long
    Dim rhsNumber As Long
    
    Set rx = CreateObject("VBScript.Regexp")
    rx.Global = True
    rx.Pattern = "_D(\d{3})_"       '// comparision criteria that will be extracted
    Set matchs = rx.Execute(lhs)
    lhsNumber = matchs(0).SubMatches(0)
    Set matchs = Nothing
    Set matchs = rx.Execute(rhs)
    rhsNumber = matchs(0).SubMatches(0)
    Set matchs = Nothing
    Set rx = Nothing
    isNewer = lhsNumber > rhsNumber
Exit Function
Error:
    If Not (rx Is Nothing) Then
        Set rx = Nothing
    End If
    err.Raise err.Number, err.Source, err.Description, err.HelpFile, err.HelpContext
End Function

Open in new window

Usage:
Dim files As Collection
Dim newestFile as String

Set files = getFilesFromFolder("c:\..........")
newestFile = getNewestFile(files)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Emily BarnesAuthor Commented:
Wow thank you! I have a very limited knowledge of VBA and macros, so this was the code I was working from, I was going to repeat to TP10.
How would your code Fabrice fit in with this, I wouldn't have to repeat/copy it for each T number?

Sub LoopThroughFolder()
   
    Dim Location As String
    Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook
    Dim Rws As Long, Rng As Range
    Dim MyPath As String
    Dim LatestFile As String
    Dim LatestDate As Date
    Dim LMD As Date
    Set Wb = ThisWorkbook
   
    Location = Sheets("Sheet1").Range("A1").Value & "\"
   
    'change the address to suite
    MyDir = Location
    MyFile = Dir(MyDir & "*TP1??????POMM.csv")       'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

   
     Do While Len(MyFile) > 0
     LMD = FileDateTime(MyPath & MyFile)
     If LMD > LatestDate Then
            LatestFile = MyFile
            LatestDate = LMD
        End If
          MyFile = Dir
       
    Loop
        Do While MyFile <> ""
        Workbooks.Open (MyFile)
        With Worksheets(1)
            Rws = .Cells(Rows.Count, "C").End(xlUp).Row
            Set Rng = Range("C4:J4")
            Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) ' "C" = column to start pasting
            ActiveWorkbook.Close True
        End With
        MyFile = Dir()
    Loop
   
    'Call Remove_Zeros
    'change the address to suite
    MyDir = Location
    MyFile = Dir(MyDir & "*TP2??????POMM.csv")       'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)
        With Worksheets(1)
            Rws = .Cells(Rows.Count, "C").End(xlUp).Row
            Set Rng = Range("C4:J4")
            Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) ' "C" = column to start pasting
            ActiveWorkbook.Close True
        End With
        MyFile = Dir()
    Loop
   
    'Call Remove_Zeros
'change the address to suite
    MyDir = Location
    MyFile = Dir(MyDir & "*TP3??????POMM.csv")       'change file extension
    ChDir MyDir
    Application.ScreenUpdating = 0
    Application.DisplayAlerts = 0

    Do While MyFile <> ""
        Workbooks.Open (MyFile)
        With Worksheets(1)
            Rws = .Cells(Rows.Count, "C").End(xlUp).Row
            Set Rng = Range("C4:J4")
            Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) ' "C" = column to start pasting
            ActiveWorkbook.Close True
        End With
        MyFile = Dir()
    Loop
   
    'Call Remove_Zeros
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.