Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2001
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto-incrementing invoice number (all versions)

    <P ID="edit" class=small>(Edited by HansV on 22-May-03 10:07. Made subject more descriptive. "Excel" as subject is not very helpful.)</P>I need to produce and excel template which has self incrementing invoice number? Thank you in advance for any ideas...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-incrementing invoice number (all versions)

    Is this template to be used in a multi-user environment, i.e. is there a chance that several users request a new invoice number at more or less the same time?

    If not, you could store the most recently assigned number in a small text file. When the user requests a new number, retrieve the number from the file, increase it by one, and store the new number.

    In a multi-user environment, you'd have to store the number in a database, for a text file is not multi-user.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-incrementing invoice number (all versions)

    I worked around the multi-user issue like this:


    <pre> Do
    lCountTimes = lCountTimes + 1
    Err.Clear
    Open sReferencePath & sCounterFileName For Input Lock Read Write As #1
    If Err.Number = 53 Or Err.Number = 75 Then
    MsgBox "Counterfile not found.", , sAppName
    Exit Sub
    End If
    If Err.Number = 70 Then
    Close #1
    Else
    For iCount = 1 To Len(sTypeList)
    Input #1, lCounterArray(iCount)
    Next
    lCounterArray(iLetter) = lCounterArray(iLetter) + 1
    For iCount = 1 To Len(sTypeList)
    Write #1, lCounterArray(iCount)
    Next
    Close #1
    bNumberOk = True
    End If
    Loop Until Err.Number = 0 Or lCountTimes = 100
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-incrementing invoice number (all versions)

    Thanks! I'll also mention this in a reply to <post#=255643>post 255643</post#> in the VBA board about a similar question for Word.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-incrementing invoice number (all versions)

    It does seem to work OK. I get no complaints about duplicate numbers.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-incrementing invoice number (all versions)

    Jan Karel,

    How can you write to a file when it is opened for input?

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-incrementing invoice number (all versions)

    Hi Hans,

    I inadvertantly omitted three lines from the code <g> This should've been it::

    Do
    lCountTimes = lCountTimes + 1
    Err.Clear
    Open sReferencePath & sCounterFileName For Input Lock Read Write As #1
    If Err.Number = 53 Or Err.Number = 75 Then
    MsgBox "Counterfile not found.", , sAppName
    Exit Sub
    End If
    If Err.Number = 70 Then
    Close #1
    Else
    For iCount = 1 To Len(sTypeList)
    Input #1, lCounterArray(iCount)
    Next
    lCounterArray(iLetter) = lCounterArray(iLetter) + 1
    Close #1
    DoEvents
    Open sReferencePath & sCounterFileName For Output Lock Read Write As #1
    For iCount = 1 To Len(sTypeList)
    Write #1, lCounterArray(iCount)
    Next
    Close #1
    bNumberOk = True
    End If
    Loop Until Err.Number = 0 Or lCountTimes = 100
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto-incrementing invoice number (all versions)

    Thanks, Jan Karel, I thought it might be something like that.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto-incrementing invoice number (all versions)

    Sorry for causing confusiog. It was a rather old piece of code, which I wrongfully edited before posting, thinking why I would close and reopen the file.

    As you can see there is a tiny chance on a conflict if a user tries to open the file whilst another user is exactly at the DoEvents command of the macro.

    Of course that could be trapped too, but I thought that chance to be too small to bother.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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