Open workbook which name is `Numbers only` , Excel VBA?-CodePudding

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

CodePudding user response：

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 = arrN(UBound(arrN))
                End If
            End If
    Next
    getLastFileName = strFold & lastName 'build the necessary path
End Function

It can be tested in the next way:

Sub testGetLastFileName()
    Debug.Print getLastFileName("C:\Teste VBA Excel\Number Files\", "*.xls*")
End Sub

It excludes workbooks name not being a number...

CodePudding user response：

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

CodePudding user response：

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.

CodePudding user response：

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