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

 

[Alex Blakenburg]

I tried it with a subfolder and it just ignored it, so it shouldn't cause an issue.

If you wrap your code inside the for loop in this If statement, you can exclude files, use AND to exclude more files.
Change the name of the file to the one you want to exclude.
You shouldn't need to use 01 but adding it won't cause any issues.

    For Each MyFile In MyFiles
    
        If UCase(MyFile.Name) <> UCase("File.xlsx") Then
        ' ...... previous code goes in here
        End If
    Next MyFile

 

[B-Man]

Error Handling worked a treat

desktop.ini was causing issues... must be hidden as I don't see it in my folder.
and also the temp file.
once excluding them it worked.
Cheers

 

[Alex Blakenburg]

You could put in some error handling so that any file that does not fit the pattern skips the steps that try to get max value section eg
(enclosed the If Result > max in a test for a number)

            If IsNumeric(Result) Then
                If Result > max Then
                    max = Result
                End If
            End If

 

[B-Man]

You could put in some error handling so that any file that does not fit the pattern skips the steps that try to get max value section eg
(enclosed the If Result > max in a test for a number)

            If IsNumeric(Result) Then
                If Result > max Then
                    max = Result
                End If
            End If

Would that still not work as the error is when its looking for the bracket to trim?
It finds number 88 fine which is the last number then errors on the first result with the bracket.

 

[Alex Blakenburg]

Good pick up, I toyed with whether I needed to give you both or not clearly I did.
Where previously I had and If statement allowing you to exclude a specific file(s) change it to this.
If the specific file did have a bracket in it than change this if statement adding "AND file_to_exclude_expression Then"

    For Each MyFile In MyFiles
        If InStr(MyFile.Name, "(") <> 0 Then

            On Error GoTo errhdlr
            '..... rest of code ....

        End If
    Next MyFile

 

[B-Man]

Good pick up, I toyed with whether I needed to give you both or not clearly I did.
Where previously I had and If statement allowing you to exclude a specific file(s) change it to this.
If the specific file did have a bracket in it than change this if statement adding "AND file_to_exclude_expression Then"

    For Each MyFile In MyFiles
        If InStr(MyFile.Name, "(") <> 0 Then

            On Error GoTo errhdlr
            '..... rest of code ....

        End If
    Next MyFile

Not at the computer until tonight so can't test.
It does what it should now anyway.
If I excluded anything that didn't have a bracket that would work too

 

[Alex Blakenburg]

That's what I just gave you does.

 

[B-Man]

This is what im using

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
   
    If InStr(MyFile.Name, "(") <> 0 Then
'        If UCase(MyFile.Name) <> UCase("Batch.xlsm") And UCase(MyFile.Name) <> UCase("Desktop.ini") And UCase(MyFile.Name) <> UCase("~$Batch.xlsm") Then

        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
        End If
    Next MyFile
  
    GetMaxBatch = max
    Exit Function
  
errhdlr:
    MsgBox "Filename Causing Error: " & MyFile.Name & "Please fix before proceeding!"
  
End Function

I call it in my vba for my userform and works a treat

    Dim fldr As String
    fldr = ThisWorkbook.Path

InputReadings.BatchNumberTextbox.Text = GetMaxBatch(fldr) + 1

I did have an issue with a file having 2 sets of brackets "test file (this is a test file) (1).xlsm"
it shouldn't have been in there but must have been a confliction with files in google drive causing it to add "(1)" to the file.

I don't really understand what the below does but can I change the below from 0 to 1? so its its less than 1 or more than 1 to ignore? I thought that's what it already done...

If InStr(MyFile.Name, "(") <> 0 Then

 

[Alex Blakenburg]

InStr is the VBA equivalent of =Find( or =Search(, it looks for that character in the string and returns its position.
When it returns 0 it means it didn't find it, in which case we want to skip that file.

A couple of changes. I initially use InStrRev which looks from right (end) to left (start) which was appropriate when we were looking for the "." just before the file extension.
Based on how we are using "(" we probably should go the normal way from left to right.
So use this line in place of the original that is there (first Result line).

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

I see you dropped my IsNumeric test. I suggest you put that back because it will catch the error you are now encountering where the file name contains "(" but the number is not in the right position.

            If IsNumeric(Result) Then
                If Result > max Then
                    max = Result
                End If
            End If

 

[B-Man]

InStr is the VBA equivalent of =Find( or =Search(, it looks for that character in the string and returns its position.
When it returns 0 it means it didn't find it, in which case we want to skip that file.

A couple of changes. I initially use InStrRev which looks from right (end) to left (start) which was appropriate when we were looking for the "." just before the file extension.
Based on how we are using "(" we probably should go the normal way from left to right.
So use this line in place of the original that is there (first Result line).

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

I see you dropped my IsNumeric test. I suggest you put that back because it will catch the error you are now encountering where the file name contains "(" but the number is not in the right position.

            If IsNumeric(Result) Then
                If Result > max Then
                    max = Result
                End If
            End If

I thought the IsNumeric would still cause an error. have now realised the other change fixed that and the IsNumeric is an added failsafe. have added it in.
While i half understand vba some things still don't quite click.

changing that line and adding IsNumeric has solved that issue.


Think I wall call it done.
onto the next set of formulas now.... its never ending!

Cheers,