Save workbooks with numbers from 1 to 100-CodePudding

I am looking for a way to save my files and add a count after each file, so file 1, next file will have 2 at the end of it and 3 then 4, and so on

ActiveWorkbook.SaveAs "C:\Martin\1BankFiles\Recon" & " " & Format(Now(), "DD-MMM-YYYY") & ".XLSX", FileFormat:=51

what to add to the above VBA code to achieve so.

CodePudding user response：

You need a way to figure out which was the last file written (and if it was written today or earlier).

As your macro stops, using a global variable is not reliable. I guess the easiest way would be to look into the folder where you write the files.

The following function will do exactly that: look for all files with the current date in the file name, figure out the highest number and return the next "free" filename.

Function GetNextFilename()
    Const BasePath = "C:\Martin\1BankFiles\"        
    Dim BaseFilename As String
    BaseFilename = "Recon " & Format(Now(), "DD-MMM-YYYY") & "_"

    Dim filename As String, filenumber As Long, largestNumber As Long
    filename = Dir(BasePath & BaseFilename & "*.xlsx")
    Do While filename <> ""
        filenumber = Val(Mid(filename, Len(BaseFilename)   1))
        If filenumber > largestNumber Then largestNumber = filenumber
        filename = Dir
    Loop
    GetNextFilename= BasePath & BaseFilename & (largestNumber   1) & ".xlsx"
End Function

Your Save-commmand would simply be

ActiveWorkbook.SaveAs GetNextFilename, FileFormat:=xlWorkbookDefault