Is it possible to auto-increment each new excel sheet with a new order number?

Question

I am building a receipt sheet in excel:

As you can see I have order number and customer number. I want to auto-increment these in a way that's most convenient for one that's gonna use the sheet. These will be protected cells that can't be altered, so therefore they need to be automated. Obviously every new receipt would need a new order number and each customer would need a new customer number. Is it even possible to do this in excel?

Answer 1

I don't think you can do what you want purely with formula, your going to need to use VBA.

You need to store the value somewhere that persists, read it and eventually update it.

There's not enough information to help with the logic behind updating and saving the value, but there are many ways to save and load a value.

You can use a plain text file, you can use Workbook.Variables or Workbook.CustomProperties or just a hidden sheet.

If this workbook is used by a single person on one computer and there is only one copy of the file it should be fairly simple to do. If not it becomes a quite a bit more complex.

If your creating a document template you will need to add something to ignore the load/update logic once the file is created so that it doesn't change the value next time you open the workbook.

Example

The following is a VBScript that will create a copy of a file "InvoiceTemplate.xlsx" as "Invoice_*.xlsx" (where * is the order number) populate the Order number cell, protect the sheet, save and then present the new file to the user.

How to use: Save the following code block as "new_invoice.vbs"

I've assumed your output file is a workbook with a single worksheet named "Invoice". Save an empty copy of your template as "InvoiceTemplate.xlsx" in the same folder as "new_invoice.vbs"

Create a folder called "invoices" in the same location

You will need to modify your template workbook, the worksheet will need a name "Invoice" and you need to add a NamedRange to the cell containing your order number (the name "InvoiceNumber"). Finally you need to unlock all cells that you want to be editable in the final document. Do not protect the template as the script does this for you on the output document.

Once you've created the script file, modified your template and created the required folder, you can double click the script file, and it should create and open a new invoice with a new number.

Sub Main()
    Dim TemplateSheetName
    TemplateSheetName = "Invoice" ' this is the name of the worksheet in your template
    Dim TemplateNamedRange
    TemplateNamedRange = "InvoiceNumber" ' the name of the cell containing the order number in your template

    Dim FSO
    Set FSO = CreateObject("Scripting.FileSystemObject")

    ' path - folder containing this script, the index file and the template file
    Dim Path
    Path = FSO.GetParentFolderName(WScript.ScriptFullName)

    ' invoicepath - destination folder, where generated invoices are saved
    Dim InvoicePath
    InvoicePath = FSO.BuildPath(Path, "invoices")

    ' indexfile - file containing the next available order number
    Dim IndexFile
    Dim IndexFilePath
    IndexFilePath = FSO.BuildPath(Path, "index.txt")

    ' templatefilepath - path to template file
    Dim TemplateFilePath
    TemplateFilePath = FSO.BuildPath(Path, "InvoiceTemplate.xlsx")

    Dim InvoiceIndex
    If Not FSO.FileExists(IndexFilePath) Then
        ' index file does not exist so create it and set to 1
        Set IndexFile = FSO.CreateTextFile(IndexFilePath)
        IndexFile.Write "1"
        IndexFile.Close
        InvoiceIndex = "1"
    Else
        ' index file exists, read the content into InvoiceIndex
        Set IndexFile = FSO.OpenTextFile(IndexFilePath)
        InvoiceIndex = IndexFile.ReadAll
        IndexFile.Close()
    End If

    ' path of file that will be generated
    Dim InvoiceFilePath
    InvoiceFilePath = FSO.BuildPath(InvoicePath, "Invoice_" & InvoiceIndex & ".xlsx")

    ' test to make sure it doesnt exist
    If Not FSO.FileExists(InvoiceFilePath) Then
        FSO.CopyFile TemplateFilePath, InvoiceFilePath
    Else
        ' output file exists already! abort
        MsgBox "IndexFile Error! Invoice #" & InvoiceIndex & " already exists. Update IndexFile"
        Exit Sub
    End If

    ' create an instance of excel
    Dim Excel
    Set Excel = CreateObject("Excel.Application")
    Excel.Visible = False 
    Dim Workbook
    ' open the copy of the template
    Set Workbook = Excel.Workbooks.Open(InvoiceFilePath)
    Dim Worksheet
    ' update the value of the NamedRange "InvoiceNumber" 
    Set Worksheet = Workbook.Worksheets(TemplateSheetName)
    Worksheet.Range(TemplateNamedRange).Value = InvoiceIndex
    ' protect the worksheet to prevent the order number from being changed
    Worksheet.Protect
    ' save changes
    Workbook.Save
    ' show the new file to the user
    Excel.Visible = True

    ' increment the index file
    Set IndexFile = FSO.CreateTextFile(IndexFilePath,True)
    IndexFile.Write InvoiceIndex + 1
    IndexFile.Close
End Sub

Main