Grabbling last filename in folder

[B-Man]

looking to grab the last filename in the folder to use the batch number so I can save the next file as batch number +1 in vba.

I used this to pull the files from the folder and it works.

Get the List of File Names from a Folder in Excel (with and without VBA)

This Excel trick uses an old Excel Function FILES() to get the list of all the File Names from a Folder in Excel (or a specific set of file names in Excel)

trumpexcel.com

but I really only need the last file so "file 12.xlsm"
is there a way I can grab only the last one or have it decending so I only have to list 1 file?



have a folder with files below
File 1.xlsm
File 2.xlsm
File 3.xlsm
File 4.xlsm
ect
File 10.xlsm
File 11.xlsm
File 12.xlsm
etc

will go up 3 digits though maybe even 4 digits
File 100.xlsm
File 101.xlsm
File 102.xlsm

 

[shknbk2]

Here's what I came up with. There is no error checking to make sure the filename is "File ##.xlsm". If it isn't, the code will error out.

Function GetMaxBatch(ByVal FolderPath As String) As Integer
    Dim max As Integer
    Dim Result As Variant
    Dim i As Integer
    Dim MyFile As Object
    Dim MyFSO As Object
    Dim MyFolder As Object
    Dim MyFiles As Object
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    Set MyFolder = MyFSO.GetFolder(FolderPath)
    Set MyFiles = MyFolder.Files
    
    max = 0
    For Each MyFile In MyFiles
        Result = Split(MyFile.Name, ".")
        Result = Split(Result(0), " ")
        If Result(1) > max Then
            max = Result(1)
        End If
    Next MyFile
    
    GetMaxBatch = max
End Function

 

[Alex Blakenburg]

@shknbk2 has done the hard yards but if you find it doesn't work because your example was an oversimplification of your actual file names, try swapping out the for each loop with this one.

    For Each MyFile In MyFiles
        'InStrRev in case there is more than 1 "." in the name
        Result = Left(MyFile.Name, InStrRev(MyFile.Name, ".") - 1)
        'Trim in case there is a space before the "."
        Result = Split(Trim(Result), " ")
        'Ubound in case there are multiple spaces in the name
        Result = Result(UBound(Result))
        If Result > max Then
            max = Result
        End If
    Next MyFile

 

[B-Man]

Thanks guys. it almost works properly...
I had to change to @Alex Blakenburg 's solution. but as I didn't explain my file structure properly its still having a little issue.

the files are structured as below

Filename Batch# (Identifying description) xxL.xlsm
so
file 87 (Simon drink total) 47L.xlsm
file 88 (Tom yearly) 99L.xlsm

 

[Alex Blakenburg]

There are a few ways of doing it.
Option 1)
Does the filename have any spaces in it ?
If not then a modified version of @shknbk2's original method would work.

Option 2)
Does the filename have any other occurences of "(" and is the occurence of the "(" something that can be relied on ?
If only 1 occurence and it can be relied on then modify my first result line replacing the "." with "("

Result = Left(MyFile.Name, InStrRev(MyFile.Name, "(") - 1)

 

[B-Man]

There are a few ways of doing it.
Option 1)
Does the filename have any spaces in it ?
If not then a modified version of @shknbk2's original method would work.

Option 2)
Does the filename have any other occurences of "(" and is the occurence of the "(" something that can be relied on ?
If only 1 occurence and it can be relied on then modify my first result line replacing the "." with "("

Result = Left(MyFile.Name, InStrRev(MyFile.Name, "(") - 1)

The only thing constant is the start of the file name. Eg.
"File"
Batch number is increasing +1 every file.
File 91
File 92
file 93

Brackets are constant but different text
And then space and then 2 numerals and "L"
If I could use the start of the bracket "(" and exclude everything after it. Then it would show "file 93" then just remove the word "file" and the spaces.

The examples in the above post is the correct layout brackets and spaces included just different text.

 

[B-Man]

Option 2)
Does the filename have any other occurences of "(" and is the occurence of the "(" something that can be relied on ?
If only 1 occurence and it can be relied on then modify my first result line replacing the "." with "("

changing the result line to (

hits a snag at the line

max = Result

error
Runtime Error 13
Mismatch

hovering over

Result = Result(UBound(Result))

gives me

UBound(Result) = <type mismatch>

 

[Alex Blakenburg]

The code is working fine on my test folder.

If you are using the code as Function, replace it with this one.
It should give you the name of the file that is causing the issue.
You most likely have a file in the folder that is not following the expected pattern.

Function GetMaxBatch(ByVal FolderPath As String) As Integer
    Dim max As Integer
    Dim Result As Variant
    Dim i As Integer
    Dim MyFile As Object
    Dim MyFSO As Object
    Dim MyFolder As Object
    Dim MyFiles As Object
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    Set MyFolder = MyFSO.GetFolder(FolderPath)
    Set MyFiles = MyFolder.Files
   
    max = 0
    For Each MyFile In MyFiles
        On Error GoTo errhdlr
        'InStrRev in case there is more than 1 "." in the name
        Result = Left(MyFile.Name, InStrRev(MyFile.Name, "(") - 1)
        'Trim in case there is a space before the "("
        Result = Split(Trim(Result), " ")
        'Ubound in case there are multiple spaces in the name
        Result = Result(UBound(Result))
        If Result > max Then
            max = Result
        End If
    Next MyFile
   
    GetMaxBatch = max
    Exit Function
   
errhdlr:
    MsgBox "Filename: " & MyFile.Name
   
End Function

I am calling it like this:

Sub GetNo()

    Dim fldr As String
    fldr = "C:\Software\Excel\Test\MrExcel Test Folder\NumericFiles"
    Debug.Print GetMaxBatch(fldr)

End Sub

My test folder looks like this

 

[B-Man]

Ok so I had a sub folder in there and that caused an issue
I moved that out of the folder and now having another issue..... Can I exclude folders?

It finds the last Batch number... 88
but getting

Runtime error 5
Invalid procedure call or argument

also i want 1 file in there as my template.
"File" can I exclude that?

 

[B-Man]

The code is working fine on my test folder.

If you are using the code as Function, replace it with this one.
It should give you the name of the file that is causing the issue.
You most likely have a file in the folder that is not following the expected pattern.

Function GetMaxBatch(ByVal FolderPath As String) As Integer
    Dim max As Integer
    Dim Result As Variant
    Dim i As Integer
    Dim MyFile As Object
    Dim MyFSO As Object
    Dim MyFolder As Object
    Dim MyFiles As Object
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    Set MyFolder = MyFSO.GetFolder(FolderPath)
    Set MyFiles = MyFolder.Files
 
    max = 0
    For Each MyFile In MyFiles
        On Error GoTo errhdlr
        'InStrRev in case there is more than 1 "." in the name
        Result = Left(MyFile.Name, InStrRev(MyFile.Name, "(") - 1)
        'Trim in case there is a space before the "("
        Result = Split(Trim(Result), " ")
        'Ubound in case there are multiple spaces in the name
        Result = Result(UBound(Result))
        If Result > max Then
            max = Result
        End If
    Next MyFile
 
    GetMaxBatch = max
    Exit Function
 
errhdlr:
    MsgBox "Filename: " & MyFile.Name
 
End Function

I am calling it like this:

Sub GetNo()

    Dim fldr As String
    fldr = "C:\Software\Excel\Test\MrExcel Test Folder\NumericFiles"
    Debug.Print GetMaxBatch(fldr)

End Sub

My test folder looks like this
View attachment 67581

just posted with one of my errors above ^

I also had to rename "test file 1 (this is a test)" etc with a leading 0
to "test file 01 (this is a test)"
as it would pull 9 instead of 88


im calling like this until i can get it to work then will implement it into my vba code

Sub CallerSub()
  Ans = GetMaxBatch("C:\Users\Admin\Batch\")
  MsgBox Ans
End Sub