Excel AutoNumber Files

[Anne Troy]

I want to create an Excel template that has a cell that contains a number that increases by one every time I OPEN the file.

Let's place the number in Sheet1!A1 and have it be formatted as 000000.

Can you provide the code?
I will add it to the knowledgebase under your name with a sample file.

[Zack Barresse]

You could put this into ThisWorkbook:

Private Sub Workbook_Open()
With Sheets("Sheet1")
    If IsEmpty(.Range("A1")) Then
        .Range("A1").Value = 0
    Else:
        .Range("A1").Value = .Range("A1").Value + 1
    End If
    End With
End Sub

An option. Many ways to skin this cat

[Scottie P]

guess I'm going too lean here but...

From the first time the workbook is opened (after installing the code):
Just Alt+F11 this into Workbook Module:

Private Sub Workbook_Open()
'Selecting the sheet where we want the count to take place
Sheets("Sheet1").Select
'Then adding by 1 each time we open the workbook
'just change the counting cell here if it changes from Cell A1
[A1].Value = [A1].Value + 1
End Sub

So if the cell is empty, a 1 will be there on open after code install.
Of course, Changes must be saved on exit or the cell value will not increment as expected.

Too lean? I'm curious.

[Zack Barresse]

Too lean? I'm curious.

Nope! Boy, I overcomplicated that one, eh?! Works great. Only thing barring is that the cell is pre-formatted in the way desired. "000000" will give you that many zeros always showing.

[Anne Troy]

Well...hey, you know me, I'm no coder. But it SEEMS that if I use this code, I'm always gonna get a ONE in that cell...I am using a TEMPLATE after all...

[Anne Troy]

Oops....so, presumably, I am saving this to a new file name each time.

[Scottie P]

hmmm...keyword "template"...

[Anne Troy]

Let's do this...

Let's have a TEXT file called c:\numbers.txt where we store the last *invoice* number.

[Scottie P]

My reading skills are superior~! 8)

[Zack Barresse]

Okay, so you want to update this text file AND the cell value by 1 everytime the book is opened?

[Anne Troy]

Well...where else ya gonna keep the last value?

[Anne Troy]

http://www.word.mvps.org/faqs/macrosvba/NumberDocs.htm

I can't get this sucker to work, but I know I have before.

[Scottie P]

Ok, this is what I came up with so far...


This will depend on a .txt in the same directory as the xl file with the name "lastvalue.txt".

This code goes in the Workbook module.

Private Sub Workbook_Open()
Dim nmbr As Long
Dim fNum As Integer
fNum = FreeFile
Open ThisWorkbook.Path & "\" & "lastvalue.txt" For Random As #fNum Len = Len(nmbr)
Get #fNum, 1, nmbr
nmbr = nmbr + 1
Put #fNum, 1, nmbr
Close #fNum
' for Cell A1
Sheets("Sheet1").Range("A1").Value = nmbr
End Sub

Remember to keep the XL file and the text file in the same directory when trying this out.

I am still tinkering with this so maybe it is a jump point for someone else.

X

[Zack Barresse]

As I told you already Scott, works great for me!

After saving it as a template file, to clear it and start again, I opened the text file, cleared the contents, saved and closed it. Then cleared the cell in the workbook template, saved and closed it out.

Works like a charm! Great stuff Scott!!!

[Scottie P]

Cool and Thanks! Really glad it works out.
Do you think that this arrangement would work in a shared environment if the read/write permissions are set on the txt file??

[Zack Barresse]

I'm not sure how well it'd work there, but I'd have to test it out. With limited testing (.txt file 'as-is') and multiple copies of the .xlt, it'll write to the .txt file anytime the book(s) is opened. That also means, even if you exit out of it and don't save it, you're still going to increment it by one. I guess this is a good reason why I'm a big fan of keeping everything in one place.

But, I haven't tested it too thoroughly yet.

[Zack Barresse]

Let me rephrase that..

The only real drawback I see, is if somebody closes out the workbook without saving it. In regards to a sequential number, it will advance regardless of the save. So multiple use's could very well be beneficial, with that one word of caution being said.

[brettdj]

or using the Registry to handle multiple filenames

Private Sub Workbook_Open()
Dim Quest      As Integer
    Sheets(1).Range("a1").Value = GetSetting("Counter", "Value", ActiveWorkbook.Name, 1)
    SaveSetting "Counter", "Value", ActiveWorkbook.Name, Sheets(1).Range("a1").Value + 1
    Quest = MsgBox("Reset Key?", vbYesNo + vbCritical)
    If Quest = vbYes Then DeleteSetting "Counter", "Value", ActiveWorkbook.Name
End Sub

Cheers

Dave

[Anne Troy]

Yeah, but using the registry doesn't allow for >1 user.

[brettdj]

Hehe

Private Sub Workbook_Open()
    Dim Quest      As Integer
    Dim CurUser As String
    CurUser = Environ("username")
    Sheets(1).Range("a1").Value = GetSetting("Counter", "Value", CurUser & " " & ActiveWorkbook.Name, 1)
    SaveSetting "Counter", "Value", CurUser & " " & ActiveWorkbook.Name, Sheets(1).Range("a1").Value + 1
    Quest = MsgBox("Reset Key?", vbYesNo + vbCritical)
    If Quest = vbYes Then DeleteSetting "Counter", "Value", CurUser & " " & ActiveWorkbook.Name
End Sub

Cheers

Dave