Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


VBA code in Excel to increment invoice number and save the workbook as "Invoice" + "InvNo"

Posted on 2013-11-02
Medium Priority
Last Modified: 2013-11-15
I had some VBA code in Excel and lost it, I will spare you the details.  I need this code again.  I need code that will increment the Invoice number which is saved in C:\Invoices\InvNo.txt and populate the next InvNo in the spreadsheet in cell F2 when opening the spreadsheet.  I have been able to do this and replicate it and it works.  Here is the part I can't remember.  I need to save each worksheet or Invoice as Invoice9999.txt in C:\Invoices\Invoice9999.txt where the 9999 portion is the same InvNo that was used to populate cell F2 when opening.  The end result is that the last InvNo is stored locally on the hard drive as well as the worksheet with the appropriate data for that InvNo.  Over time, the local directory should have all the Invoices created stored as txt files with the InvNo showing in the name.  Thanks for any help you can provide. The InvNo gets incremented at the opening of the spreadsheet and the file should be saved as a txt file when the spreadsheet is saved after data entry is completed.  Thank you very much.
Question by:mjchevalier
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 81

Expert Comment

ID: 39619471
I adapted code written by Microsoft Excel MVP J.E. McGimpsey as shown below. The function NextSeqNumber returns the next invoice number, reading the specified text file and writing as requested. The Workbook_Open sub updates the invoice number when the workbook is opened. The SaveMe macro saves the workbook as a text file in the specified location and with the specified name.
'This function should be installed in a regular module sheet
Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long
        Const sDEFAULT_PATH As String = "C:\Invoices\InvNo.txt "
        Const sDEFAULT_FNAME As String = "InvNo.txt"
        Dim nFileNumber As Long
        nFileNumber = FreeFile
        If sFileName = "" Then sFileName = sDEFAULT_FNAME
        If InStr(sFileName, Application.PathSeparator) = 0 Then _
            sFileName = sDEFAULT_PATH & Application.PathSeparator & sFileName
        If nSeqNumber = -1& Then
            If Dir(sFileName) <> "" Then
                Open sFileName For Input As nFileNumber
                Input #nFileNumber, nSeqNumber
                nSeqNumber = nSeqNumber + 1&
                Close nFileNumber
                nSeqNumber = 1&
            End If
        End If
        On Error GoTo PathError
        Open sFileName For Output As nFileNumber
        On Error GoTo 0
        Print #nFileNumber, nSeqNumber
        Close nFileNumber
        NextSeqNumber = nSeqNumber
        Exit Function
        NextSeqNumber = -1&
    End Function
Sub SaveMe()
Dim InvNo As Long
InvNo = ThisWorkbook.Worksheets(1).Range("F2").Value
ThisWorkbook.SaveAs "C:\Invoices\Invoice" & InvNo & ".txt", FileFormat:=20       'Save file as Windows text file
End Sub

'This sub must go in ThisWorkbook code pane. It won't work at all if installed anywhere else!
Public Sub Workbook_Open()
    ThisWorkbook.Sheets(1).Range("F2").Value = NextSeqNumber
End Sub

Open in new window

LVL 16

Accepted Solution

Peter Kwan earned 2000 total points
ID: 39619483
You may try adding the following code to your workbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
        Cancel = True
        Application.EnableEvents = False
        InvoiceNo = ActiveSheet.Cells(2, 6)
        Open "C:\Invoices\Invoice" & InvoiceNo & ".txt" For Output As #1
        For Each rngRow In ActiveSheet.UsedRange.Rows
            strDelimiter = ""
            For Each rngCol In rngRow.Cells
                Print #1, strDelimiter & rngCol.Value;
                strDelimiter = "|"
            Print #1, ""
        Close #1
        Application.EnableEvents = True
End Sub

Open in new window

It works for Excel 2007.
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39619774

As an alternate method, instead of recording multiple files that reference the most recent reference number, here is a question I contributed to in 2003:

"get data from a file, increment and save back to file"
[ ]

Here I provided a routine to return the "next invoice number" (that could be returned to cell [F2], for example).

To achieve this, a file ("h:\invoice.txt") is maintained that is initially read & the previously recorded value stored is obtained.  This value is returned, & the value within the file is updated by a value of 1 (one).

The file, therefore, always stores the "next invoice number".

Cell [F2] would contain the formula: =Get_Next_Invoice_Number()

It would be a simple task to amend this routine to read the file, increment by one, update the file, then return the incremented value instead.

The file would then contain the "current invoice number".

The filename could also be amended to a name like you originally intended ("Invoice{Number}.txt"), if desired.

Would you require multiple files to be maintained within the same folder if you know that the only file that exists in the pre-defined location includes the most recent (or "next") value?

(Either of the two proposals provided above could be amended to remove "old" files when a new file is created, for instance).

Either way, there is further discussion in the original thread if you wish to look at the other suggestions made at that time.


LVL 35

Expert Comment

by:[ fanpages ]
ID: 39652669
You're welcome.

Featured Post

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question