Auto-incrementing invoice number (all versions)

Topic

New Reply

(Edited by HansV on 22-May-03 10:07. Made subject more descriptive. “Excel” as subject is not very helpful.)

I need to produce and excel template which has self incrementing invoice number? Thank you in advance for any ideas…

Reply | Quote

Replies

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.

Reply | Quote

I worked around the multi-user issue like this:

        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

Reply | Quote

Thanks! I’ll also mention this in a reply to post 255643 in the VBA board about a similar question for Word.

Reply | Quote

It does seem to work OK. I get no complaints about duplicate numbers.

Reply | Quote

Jan Karel,

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

Reply | Quote

Hi Hans,

I inadvertantly omitted three lines from the code 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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote