The below function do the following:
Get the full path of file which name is numbers only highest number , on current Folder.
It is works normally until file name is greater than 2139999999 , e.g 2149999999.xlsb
The error raised on this line If lngNb < CLng(Split(El, ".")(0)) Then
Note: I am using office 2016 32Bit on windows 10 64Bit.
Appreciate for yours comments and answers.

Function Path_of_Highest_Numeric_Name(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
            Path_of_Highest_Numeric_Name = strFold & lastName 'Build the necessary path
End Function

It can be tested in the next way:

Debug.Print Path_of_Highest_Numeric_Name("C:\Users\Waleed\Desktop\", "*.xls*")

CodePudding user response：

The maximum number a Long can hold (as it is a signed 32bit number) is 2,147,483,647 - therefore you get the overflow.

You could, as Rory suggest, switch to a Double as it has more significant digits.
I would suggest you don't deal with numbers at all - at the end you return a string anyhow.

The following logic compares strings. To prevent that "9" > "10", the logic is if a string is longer, it is larger. If both strings have the same length, you can compare them.

The function IsNumeric, btw, works correctly no matter how long your string (=filename) is.

Dim largestNumber As String, newNumber As String, fileWithLargestNumber As String
largestNumber = ""

For Each El In arrD   'iterate between the array elements
    newNumber = Split(El, ".")(0)
    If IsNumeric(newNumber) Then
        If Len(largestNumber) < Len(newNumber) _
        Or (Len(largestNumber) = Len(newNumber) And largestNumber < newNumber) Then
            largestNumber = newNumber
            fileWithLargestNumber = El
        End If
    End If
Next

Path_of_Highest_Numeric_Name = strFold & fileWithLargestNumber