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.
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
strUniqueNumber = CreateObject(“Scriptlet.TypeLib”).GUID
Another approach, with a little more flexibility:
http://www.mcgimpsey.com/excel/invoice.html
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
Penny: That’s sounds right to me. If it doesn’t work, let me know.
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**
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