Excel VBA: Increment an Invoice Number When a Workbook Opens
Question details
The user needs an invoice number to increase automatically whenever an Excel workbook opens, including when changes to the workbook are not saved.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- An invoice workbook should read the latest invoice number, add one, and save the updated value in a persistent external location.
- Observed behavior
- A number stored only in the workbook can be lost when the workbook closes without being saved, so the next opening may reuse the previous invoice number.
Create a dedicated folder for the persistent invoice-number file and decide which worksheet cell should display the new number. Confirm that macros are enabled and that the workbook is saved as an Excel Macro-Enabled Workbook (*.xlsm).
Store the invoice number in an external text file with Workbook_Open
Use this method when the workbook itself may remain unsaved but the invoice number must persist between openings.
The Workbook_Open event runs when the workbook opens. Because the last number is stored in a separate text file, the value remains available even if the invoice workbook is closed without saving.
Update the worksheet name, target cell, and file path in the example before using it. Avoid opening the same workbook simultaneously from multiple users because two instances could read the same value before either writes the next one.
In Excel, press Alt+F11 to open the Visual Basic Editor, expand your workbook in Project Explorer, and double-click ThisWorkbook.
Paste this code into the ThisWorkbook module, then change the file path, worksheet name, and target cell as needed: Private Sub Workbook_Open()\n Dim filePath As String\n Dim lastNumber As Long\n Dim invoiceNumber As Long\n Dim fileNumber As Integer\n\n filePath = "C:\\InvoiceData\\last-invoice-number.txt"\n\n If Dir(filePath) <> "" Then\n fileNumber = FreeFile\n Open filePath For Input As #fileNumber\n Input #fileNumber, lastNumber\n Close #fileNumber\n Else\n lastNumber = 0\n End If\n\n invoiceNumber = lastNumber + 1\n ThisWorkbook.Worksheets("Invoice").Range("B2").Value = invoiceNumber\n\n fileNumber = FreeFile\n Open filePath For Output As #fileNumber\n Print #fileNumber, invoiceNumber\n Close #fileNumber\nEnd Sub
Create the folder used in filePath, save the workbook as an .xlsm file, close it, and open it again. Check that the number in Invoice!B2 increases and that the text file contains the same latest number.
Keep the counter in a separate Excel workbook
Use a separate counter workbook when you want the stored invoice number to remain editable and visible in Excel rather than in a text file.
Use WPS Office as a Lightweight Alternative for Invoice Workbooks
WPS Office is a free, lightweight alternative for creating, editing, and reviewing invoice spreadsheets. It provides a familiar spreadsheet interface, supports common Microsoft Excel formats, and makes it easier to migrate everyday workbook tasks without changing your document workflow. Excel-specific VBA automation may still require Microsoft Excel for full macro compatibility.
- 1. Install WPS Office: Download and install WPS Office, then open the WPS Spreadsheet application.
- 2. Open the invoice file: Choose Menu > Open and select the existing .xlsx, .xls, or .xlsm invoice workbook.
- 3. Review compatibility: Check the invoice layout, formulas, external links, and any macro-dependent features before using the file as a replacement workflow.
Frequently Asked Questions
Can Workbook_Open increment a number if the Excel workbook is not saved?
Yes, but the updated number must be written to an external persistent location such as a text file, another workbook, or a database. A change held only in an unsaved workbook is lost when the file closes.
Where should the VBA code be placed?
Press Alt+F11, open the workbook project, and place the procedure in the ThisWorkbook module. The Workbook_Open event runs automatically when that workbook opens.
What happens if the external invoice-number file does not exist?
The macro can treat the starting value as zero, create the external file, and write the first invoice number. The folder must already exist and allow the user to create files.
Can two users safely open the invoice workbook at the same time?
Not with a basic read-then-write macro. Both users could read the same last number and generate duplicates. Use a database or another controlled numbering service when multiple users need concurrent access.
Why does my macro not run when I open the workbook?
Check that the code is in ThisWorkbook rather than a standard module, that macros are enabled under Excel's security settings, and that the file is saved as .xlsm instead of .xlsx.
