This very simple macro adds a number to a document and saves with the file name in the format of "inv1.docx", creating a very simple numbering system for invoices.
Step 1: Create a folder on your hard drive. Create a text file named invoice-number.txt in the folder. To begin with 1, leave the file blank.
To start with a specific number, add this to the text file and save, using one less than the desired number.
[InvoiceNumber]
Invoice=140072
Step 2: Open Word then press Alt+F11 to open the VB Editor.
Step 3: Expand Microsoft Word Documents then double click on ThisDocument. Paste the code below into ThisDocument. Change the file paths as needed.
Sub CreateInvoiceNumber()
Invoice = System.PrivateProfileString("C:\Users\user\Documents\a\" & _
"invoice-number.txt", "InvoiceNumber", "Invoice")
If Invoice = "" Then
Invoice = 1
Else
Invoice = Invoice + 1
End If
System.PrivateProfileString("C:\Users\user\Documents\a\" & _
"invoice-number.txt", "InvoiceNumber", "Invoice") = Invoice
' Insert the number in the document
ActiveDocument.Range.InsertBefore Format(Invoice, "#")
ActiveDocument.SaveAs2 FileName:= _
"C:\Users\user\Documents\a\inv" & Format(Invoice, "#") & ".docx" _
, FileFormat:=wdFormatXMLDocument, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False, CompatibilityMode:=14
End Sub
Step 4: Run the macro.
To place the number in a specific position (this macro adds at the very beginning of the page), create a bookmark in the template named Invoice then use this code to insert the number:
ActiveDocument.Bookmarks("Invoice").Range.InsertBefore Format(Invoice, "#")
More Information
Function to Create a serial number (from VBOffice.net)
Great code! My Invoice variable in my text document is not updating. I do not see any code that saves the text file after updating the Invoice Variable. Do you have any suggestions? Thank you.
where and how to use this code
ActiveDocument.Bookmarks("Invoice").Range.InsertBefore Format(Invoice, "#")
This goes into a Word document that has a bookmark named Invoice.
Just testing this out, but I keep getting a 448 error when I try to run it. It says "Named argument not found". I'm using a Mac BTW. I've only changed the filepath - my text file is contained in the folder "LOL". Not really sure why it isn't working. Sub CreateInvoiceNumber() Invoice = System.PrivateProfileString("C:/Users/angusho/Downloads/LOL" & _ "invoice-number.txt", "InvoiceNumber", "Invoice") If Invoice = "" Then Invoice = 1 Else Invoice = Invoice + 1 End If System.PrivateProfileString("C:/Users/angusho/Downloads/LOL" & _ "invoice-number.txt", "InvoiceNumber", "Invoice") = Invoice ' Insert the number in the document ActiveDocument.Range.InsertBefore Format(Invoice, "#") ActiveDocument.SaveAs2 FileName:= _ "C:/Users/angusho/Downloads/LOL" & Format(Invoice,… Read more »
Best guess: it's the system.privateprofilesting. That might be windows only. I'll have to take a look at making it work on mac.
very nice code, it works flawlessly for me after fidgeting around with it.
only have 1 small problem.
what if I want to start an invoice number from 0000?
It does not let me do that, it will always start at 1 instead of adding the other 3 digits and starting from 0001
If you use
[InvoiceNumber]
Invoice=0001
and remove the if lines below - and just use Invoice = Invoice + 1 does it work?
If Invoice = "" Then
Invoice = 1
Else
Invoice = Invoice + 1
End If
I've got this to work, sort of. The only problem is that it doesn't overwrite the previous number. So the first invoice has 1, then the next has 21, then the next has 321, then 4321, etc.
If there is an invoice number in the file, it won't replace it, not. But you shouldn't have an invoice # in the file before running the macro, except maybe during testing.