Incrementing Invoice Numbers with a Text File

Whenever I create an invoice, I need to assign it a unique number. There are several ways to do this (I’ll discuss other ways in future posts), but the way that I describe here uses a text file. I’ve placed a text file in a shared directory so I can use this from any computer. The text file holds the last invoice number that I’ve used. When I create another invoice, it writes the new invoice number (old invoice number + 1) back to the text file.

Sub NextInv()

    Dim FName As String
    Dim FNum As Long
    Dim LstInv As String
    
    FName = sMYPATH & “LastInv.txt”
    FNum = FreeFile
    
    Open FName For Input As FNum
    Input #FNum, LstInv
    
    Close FNum
    
    Range(“B18?).Value = CLng(LstInv) + 1
    
    Open FName For Output As FNum
    Print #FNum, CLng(LstInv) + 1
    Close FNum

End Sub

This example is pretty bare-bones. There’s no error checking to determine if someone has deleted the file, for instance. It also lacks the ability to maintain a proper sequence. I can get a new invoice number, but if I don’t save that invoice, I’ll have skipped a number.

For my purposes, the text file is deep in a directory (security by obscurity) and I don’t really care if my invoices are a little out of sequence, just that they’re unique and reasonably identifiable (that is, they’re not crazy random numbers).

I suppose to ensure the sequence is maintained, the BeforeSave event could be used to write back to the text file.

Posted in Uncategorized

7 thoughts on “Incrementing Invoice Numbers with a Text File

  1. Hi Dick
    just my version using the Windows registry and some code in the workbook_open event of a template:
    – It changes cell A1 of the first sheet
    – you may change the key identifiert according to your needs (e.g. DEFAULTSTART, MYLOCATION, etc.)

    Private Sub Workbook_Open()
    Const DEFAULTSTART As Integer = 1
    Const MYAPPLICATION As String = “Excel”
    Const MYSECTION As String = “myInvoice”
    Const MYKEY As String = “myInvoiceKey”
    Const MYLOCATION As String = “A1?
    Dim regValue As Long

    With ThisWorkbook.Sheets(1).Range (MYLOCATION)
    If .Text <> “” Then Exit Sub
    regValue = GetSetting(MYAPPLICATION, MYSECTION, _
    MYKEY, DEFAULTSTART)
    .Value = CStr(regValue)
    SaveSetting MYAPPLICATION, MYSECTION, MYKEY, regValue + 1
    End With
    End Sub

    Also no real error checking included, etc.

    Frank

  2. I am trying to use Franks Code, But I need the invoice to start at #16160, so it right to put this # in this spot

    CONST DEFAULTSTART As Integer = 16160

    Any replies will be greatly appreciated

  3. Thanx for the help the code ye posted was very helpfull… both of the approaches. I decided to go with the text file one but both approaches were cool.
    I did modify it a bit and added a line on the
    Worksheet open section so it will auto increase every time I open the document.
    Run (NextInv)
    ya very simple :P (I’m lazy )
    Sure it may be a hasle when you open the document several times it will always increase the number but heck it will serve our purposes for now. Thanx a lot for all yer efforts and yer coding i’ve been out of the programming game for so long, having to figgure this kinda thng again will probly take me twise as much time than I reely have in my current line of work (Graphic Designer) **cheers**

  4. Hi,
    I have created a quote in excel, and each quote has a unique number. I would like to be able to open the template and it have the next sequentual number in the spreadsheet. I would also like the document to save automatically with “Customer Name, date, Quote Number” Is this possible????I have tried using the above codes sct, however I have no idea what I am doing, and nothing seems to work. Is there someone that could possibly help me. Many Thanks In Advance…..Tania


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.