VBA code in Excel to increment invoice number and save the workbook as "Invoice" + "InvNo"

I adapted code written by Microsoft Excel MVP J.E. McGimpsey as shown below. The function NextSeqNumber returns the next invoice number, reading the specified text file and writing as requested. The Workbook_Open sub updates the invoice number when the workbook is opened. The SaveMe macro saves the workbook as a text file in the specified location and with the specified name.

'This function should be installed in a regular module sheet
Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
        Const sDEFAULT_PATH As String = "C:\Invoices\InvNo.txt "
        Const sDEFAULT_FNAME As String = "InvNo.txt"
        Dim nFileNumber As Long
        
        nFileNumber = FreeFile
        If sFileName = "" Then sFileName = sDEFAULT_FNAME
        If InStr(sFileName, Application.PathSeparator) = 0 Then _
            sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
        If nSeqNumber = -1& Then
            If Dir(sFileName) <> "" Then
                Open sFileName For Input As nFileNumber
                Input #nFileNumber, nSeqNumber
                nSeqNumber = nSeqNumber + 1&
                Close nFileNumber
            Else
                nSeqNumber = 1&
            End If
        End If
        On Error GoTo PathError
        Open sFileName For Output As nFileNumber
        On Error GoTo 0
        Print #nFileNumber, nSeqNumber
        Close nFileNumber
        NextSeqNumber = nSeqNumber
        Exit Function
PathError:
        NextSeqNumber = -1&
    End Function
    
Sub SaveMe()
Dim InvNo As Long
InvNo = ThisWorkbook.Worksheets(1).Range("F2").Value
ThisWorkbook.SaveAs "C:\Invoices\Invoice" & InvNo & ".txt", FileFormat:=20       'Save file as Windows text file
End Sub


'This sub must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Public Sub Workbook_Open()
    ThisWorkbook.Sheets(1).Range("F2").Value = NextSeqNumber
End Sub

Select allOpen in new window

Hi,

As an alternate method, instead of recording multiple files that reference the most recent reference number, here is a question I contributed to in 2003:

"get data from a file, increment and save back to file"
[ https://www.experts-exchange.com/questions/20742111/get-data-from-a-file-increment-and-save-back-to-file.html?anchorAnswerId=9391947#a9391947 ]

Here I provided a routine to return the "next invoice number" (that could be returned to cell [F2], for example).

To achieve this, a file ("h:\invoice.txt") is maintained that is initially read & the previously recorded value stored is obtained.  This value is returned, & the value within the file is updated by a value of 1 (one).

The file, therefore, always stores the "next invoice number".

Cell [F2] would contain the formula: =Get_Next_Invoice_Number()


It would be a simple task to amend this routine to read the file, increment by one, update the file, then return the incremented value instead.

The file would then contain the "current invoice number".

The filename could also be amended to a name like you originally intended ("Invoice{Number}.txt"), if desired.

Would you require multiple files to be maintained within the same folder if you know that the only file that exists in the pre-defined location includes the most recent (or "next") value?

(Either of the two proposals provided above could be amended to remove "old" files when a new file is created, for instance).

Either way, there is further discussion in the original thread if you wish to look at the other suggestions made at that time.

BFN,

fp.

You're welcome.