Question on autonumber

[Xenos]

I’m sure this have been asked a thousand times, but I just need a starting hand. I have built a quote sheet and now I want to have the quote number automatically update with the next available number. I am thinking I can put a TXT file in the same dir and then call it from excel but I don’t know where to write. I want to call it Onopen.

I am thinking it should look like this. ( I’m a novice with code.)

Breneck Quote Sheet 2.Main.Range (“C17”).Value = NextSeqNumber (“Quote.txt”)

Am I on the right track?

[Gareth]

There's a number of ways - it would probably depend on how you operate
and the number of users using the workbook. You could store it in the
workbook properties or even in the cell itself and just increment it.

However, I guess you have multiple users and therefore people might be
opening it as read only therefore you want to store it elsewhere. In
that case, to write/read from a file so something like the below.

Note that this obviously doesn't prevent quote numbers from not being
used when someone just opens and closes a workbook for no reason.

HTH,
Gareth

In your ThisWorkbook code module place:

'assuming your worksheet is named 'Breneck Quote'
Private Sub Workbook_Open
Thisworkbook.sheets("Breneck Quote").Range ("C17").Value _
= fcnGetNextAvailableQuoteNumber
End Sub

Private Function fcnGetNextAvailableQuoteNumber() as string
Const myFileName as string = "quotenumber.txt"
Dim F as long
Dim MyNo as Variant

'get the quote number
If Dir(ThisWorkbook.Path & "\" & myFileName) <> "" Then
F = FreeFile
Open Thisworkbook.path & "\" & myFileName _
for input as #F
Input #F, MyNo
Close #F
End if

'Set to 1 if it was empty (or something else was in the file)
If not isnumeric(myno) then MyNo = 1 else MyNo = MyNo +1

'return the number (I assume you want it formatted with
'zeros or something
fcnGetNextAvailableQuoteNumber = Format(MyNo,"00000000")

'update the file
F = FreeFile
Open Thisworkbook.path & "\" & myFileName _
for output as #F
Print #F, MyNo
Close #F

End function


Xenos wrote:
> I’m sure this have been asked a thousand times, but I just need a
> starting hand. I have built a quote sheet and now I want to have the
> quote number automatically update with the next available number. I am
> thinking I can put a TXT file in the same dir and then call it from
> excel but I don’t know where to write. I want to call it Onopen.
>
> I am thinking it should look like this. ( I’m a novice with code.)
>
> Breneck Quote Sheet 2.Main.Range (“C17”).Value = NextSeqNumber
> (“Quote.txt”)
>
> Am I on the right track?
>
>

[Xenos]

Gareth, Thank you… it rocks.

[Gareth]

You're welcome mate.

Xenos wrote:
> Gareth, Thank you… it rocks.
>
>

[Xenos]

Gearth, how do I direct the txt file to c:\Quote ?

[Gareth]

Hi,

Replace the function fcnGetNextAvailableQuoteNumber I gave you with the
below one.

FYI - I just changed
Const myFileName as string = "quotenumber.txt"
to
Const myFileName as string = "c:\quote\quotenumber.txt"

and instead of using ThisWorkbook.Path & "\" & myFileName I use just
myFilename.

G

Private Function fcnGetNextAvailableQuoteNumber() as string
Const myFileName as string = "c:\quote\quotenumber.txt"
Dim F as long
Dim MyNo as Variant

'get the quote number
If Dir(myFileName) <> "" Then
F = FreeFile
Open myFileName for input as #F
Input #F, MyNo
Close #F
End if

'Set to 1 if it was empty (or something else was in the file)
If not isnumeric(myno) then MyNo = 1 else MyNo = MyNo +1

'return the number (I assume you want it formatted with
'zeros or something
fcnGetNextAvailableQuoteNumber = Format(MyNo,"00000000")

'update the file
F = FreeFile
Open myFileName for output as #F
Print #F, MyNo
Close #F

End function

Xenos wrote:
> Gearth, how do I direct the txt file to c:\Quote ?
>
>