Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 2 of 2

Thread: Increment invoice number when opening template and save as new file name

  1. #1
    Join Date
    12th August 2011

    Increment invoice number when opening template and save as new file name

    I want to make an invoice template and have others pull up the template and each time they do have it add one to the invoice number. Then I want them to populate the invoice and save it under a different name. My problem is when they open the file under the different name it adds one to the invoice as well. How can I make it so the saved copy invoice number is locked? Also if I wanted to start at a number say 4000 how would I accomplish this. The code in my work book is as follows: I am a novice at VBA and macros.
    Public Function NextSeqNumber(Optional sFileName As String, Optional nSeqNumber As Long = -1) As Long 
        Const sDEFAULT_PATH As String = "C:\Documents and Settings\....." 
        Const sDEFAULT_FNAME As String = "C:\Documents and Settings\.....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& 
    Last edited by AAE; August 12th, 2011 at 08:54. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports

  2. #2
    Join Date
    19th January 2007
    South Carolina, USA

    Re: Increment invoice number when opening template and save as new file name

    Hello psu41,

    Welcome to Ozgrid. We're glad to have you on board, however, please note the following regarding thread titles:

    Thread titles are used in searching the forum, therefore, it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title use as search terms would return relevant results.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters (e.g. slashes, commas, colons, etc)
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different
    • The title should not contain special characters such as < > ~ ! @ # $ % ^ & * ( ) or math operators - these are not search friendly

    Your title of "Sequential numbers problems" does not describe your thread or objective and is not helpful to those searching the forum for a solution to a similar need.

    Please note the change to your title, which is based on the objective stated in your thread.

    If the new title still does not accurately describe your thread you may make further edits as needed per the above guidelines.

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. sequential numbers in other sheet
    By ashokkumarkolla in forum Excel General
    Replies: 1
    Last Post: May 17th, 2011, 20:40
  2. Check & Add Sequential Numbers In Column
    By jerrychem in forum Excel General
    Replies: 8
    Last Post: February 9th, 2008, 03:18
  3. Update Sequential Numbers After Row Deletion
    By Scorp25 in forum Excel General
    Replies: 6
    Last Post: October 16th, 2007, 09:40
  4. Add Sequential Numbers To Range
    By Jeff P in forum Excel General
    Replies: 11
    Last Post: August 17th, 2007, 15:25
  5. Add Sequential Numbers to List Items
    By excel_for_fun in forum Excel General
    Replies: 3
    Last Post: September 20th, 2004, 11:09


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts