Get the full path of file name which is numbers only `highest number` , on main Folder or Subfolders?

Question

I am using database software and export excel files from it and save on my desktop with name like ********.xls
***** means numbers only , for example 08134471.xls
23222578.xls
Those numbers on file name are random on count and length.
The path is constant D:\Users\Waleed\Desktop\ but, file name will change each time incrementally to higher number.
So, I need to open the workbook with highest number on the cited path.
In advance , gratefully for your help.

Sub Open_Numeric_File()
 
    Workbooks.Open "D:\Users\Waleed\Desktop\08134471.xls"
 
End Sub

Answer 1

Please try the next function. It will return independent of the number name pattern. I mean, being "0002345" or "02346", It processes all files from folder and subfolders:

Function getLastFileName(strFold As String, Optional strext As String = "*.*") As String
    Dim arrD, i As Long, lastName As String, lngNb As Long, arrN, El
    'return all files name in an array
    arrD = Filter(Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b /s").StdOut.ReadAll, vbCrLf), "\")
    For Each El In arrD   'iterate between the array elements
            arrN = Split(El, "\") 'make an array splitting the name by "\"
            'check if the name is numeric:
            If IsNumeric(Split(arrN(UBound(arrN)), ".")(0)) Then
                'compare the lngNb variable (initially 0) with the numeric value:
                If lngNb < CLng(Split(arrN(UBound(arrN)), ".")(0)) Then
                     'addapt lngNb like the bigger number
                     lngNb = CLng(Split(arrN(UBound(arrN)), ".")(0)): lastName = El
                End If
            End If
    Next
    getLastFileName = lastName 'build the necessary path
End Function

It can be tested in the next way:

Sub testGetLastFileName()
    Debug.Print getLastFileName("D:\Users\Waleed\Desktop\", "*.xls*")
End Sub

It excludes workbooks name not being a number...

Edited:

The next version returns (and process) only the files in the main folder (excluding files in subfolders):

Function getLastNumberFile(strFold As String, Optional strext As String = "*.*") As String
    Dim arrD, i As Long, lastName As String, lngNb As Long, El
    'return all files name in an array
    arrD = Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b").StdOut.ReadAll, vbCrLf)
    If UBound(arrD) = -1 Then MsgBox "Nothing could be found in the path you supplied...": Exit Function
    arrD(UBound(arrD)) = "@@##": arrD = Filter(arrD, "@@##", False) 'remove the last (empty) element

    For Each El In arrD   'iterate between the array elements
            If IsNumeric(Split(El, ".")(0)) Then
                'compare the lngNb variable (initially 0) with the numeric value:
                If lngNb < CLng(Split(El, ".")(0)) Then
                     'addapt lngNb like the bigger number
                     lngNb = CLng(Split(El, ".")(0)): lastName = El
                End If
            End If
    Next
    getLastNumberFile = strFold & lastName 'build the necessary path
End Function

It can be used simple setting the necessary workbook as:

  Set wb1 = Workbooks.Open(getLastNumberFile("D:\Users\Waleed\Desktop\", "*.xls*"))

And the next version, able to return for both cases. It uses a trick, splitting a joined array using the separator obtained by concatenation between the folder path and "|". Then removing the last array element (in case of returning without subfolders files):

Function getLastFileN(strFold As String, Optional strext As String = "*.*", Optional boolSubfolders = False) As String
    Dim arrD, i As Long, lastName As String, lngNb As Long, arrN, El
    'return all files name in an array
    If boolSubfolders Then 'subfolders included
        arrD = Filter(Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b/s").StdOut.ReadAll, vbCrLf), "\")
    Else                   'without subfolders
        arrD = Split(CreateObject("wscript.shell").Exec("cmd /c dir """ & strFold & strext & """ /b").StdOut.ReadAll, vbCrLf)
        arrD = Split(strFold & Join(arrD, "|" & strFold), "|")  'add the folder path to the file names
        arrD(UBound(arrD)) = "@@##": arrD = Filter(arrD, "@@##", False) 'remove the last (empty) array element
    End If

    For Each El In arrD           'iterate between the array elements
            arrN = Split(El, "\") 'make an array splitting the name by "\"
            'check if the name is numeric:
            If IsNumeric(Split(arrN(UBound(arrN)), ".")(0)) Then
                'compare the lngNb variable (initially 0) with the numeric value:
                If lngNb < CLng(Split(arrN(UBound(arrN)), ".")(0)) Then
                     'addapt lngNb like the bigger number
                     lngNb = CLng(Split(arrN(UBound(arrN)), ".")(0)): lastName = El
                End If
            End If
    Next
    getLastFileN = lastName
End Function

Answer 2

Something for you to consider:

Sub OpenAndCalc()

Dim myDir As String, fn As String, high As String, highVal As Long

    myDir = "D:\Users\Waleed\Desktop\"
    fn = Dir(myDir & "*.xls")
    
    Do While fn <> "" And Not fn Like "*[!0-9]*.xls"
        If Val(fn) > highVal Then highVal = Val(fn): high = fn
        fn = Dir()
    Loop

    Workbooks.Open myDir & high
    
End Sub

This should:

• Loop through all '.xls' files in your static directory;
• Test if anything before '.xls' is numeric through Like();
• Test the numeric substring, using Val(), against previous (or empty) value;
• Append the static directory with the highest value and open this workbook.

Answer 3

Or something like this:

Uses the VBA Dir() function to loop over all the .xls files (not directories etc) in a folder. It splits out the text in front off the ".", and attempts to convert this into a number, while keeping a track of the maximum number found.

If on a 32-bit system replace LongLong with Long, and CLngLng with CLng.

Sub ListFiles()
    Dim llFile As LongLong
    Dim llMax As LongLong
    Dim strFileToOpen As String
    Dim strPath As String
    Dim strFile As String
    
    strPath = Environ("USERPROFILE") & "\Desktop\*.xls"
    strFile = Dir(strPath, vbNormal)
       
    On Error Resume Next
    
    While Len(strFile) > 0
        llFile = 0
        llFile = CLngLng(Split(strFile, ".")(0))
        If llFile > 0 And llFile > llMax Then
            llMax = llFile
            strFileToOpen = strFile
        End If
               
        strFile = Dir()
    Wend

    On Error Goto 0
    
    If Len(strFileToOpen) > 0 Then Workbooks.Open (strPath & "\" & strFileToOpen)
End Sub

Answer 4

You could do a list of all of the files in the folder, put them into a sheet / array and then sort them by the file name

There's a really good piece of code below that came from VBATips

Dim iRow

Sub ListFiles()
    iRow = 11
    Call ListMyFiles(Range("C7"), Range("C8"))
End Sub

Sub ListMyFiles(mySourcePath, IncludeSubfolders)
    Set MyObject = New Scripting.FileSystemObject
    Set mySource = MyObject.GetFolder(mySourcePath)
    On Error Resume Next
    For Each myFile In mySource.Files
        iCol = 2
        Cells(iRow, iCol).Value = myFile.Path
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Name
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.Size
        iCol = iCol + 1
        Cells(iRow, iCol).Value = myFile.DateLastModified
        iRow = iRow + 1
    Next
    If IncludeSubfolders Then
        For Each mySubFolder In mySource.SubFolders
            Call ListMyFiles(mySubFolder.Path, True)
        Next
    End If
End Sub

This will list all of the files in the folder into Excel, you could change the to an array

After this, just sort them on the myFile.DateLastModified

Sorry this isn't a full answer, had a minute free and thought this might help you

Partial Source: http://excelexperts.com/VBA-Tips-List-Files-In-A-Folder