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

[Anne Troy]

From the lovely NateO:



Public y As Date

Private Sub Workbook_Open()
y = Now
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ThisWorkbook.BuiltinDocumentProperties("Total Editing Time") = _
(Now - y) * 1440
y = Now
ThisWorkbook.BuiltinDocumentProperties("Revision Number") = _
ThisWorkbook.BuiltinDocumentProperties("Revision Number") + 1
End Sub


This block of code must go into the workbook class module. To easily access this, right-click on the Excel icon beside your file menu heading. Now left-click view code and paste the procedure.

Basically it stores the time the file was opened or last saved in a variable (y) and, when saving, calculates the difference in minutes and tacks one onto the exisiting revision number.
Of course, I have no idea how this would follow, but I assume I'll find out. :)

[NateO]

Hello, based on the quandary at hand, let's drop the revision time and assign the integer to the Document Property on the workbook's open. I.e.,



Private Sub Workbook_Open()
ThisWorkbook.BuiltinDocumentProperties("Revision Number") = _
ThisWorkbook.BuiltinDocumentProperties("Revision Number") + 1
End Sub

As Anne quoted me, this belongs in the Workbook Class Module. The number now travels with the Workbook. :)

How does one access the number (in the specified format) you ask? Another procedure can do this. E.g.,



Sub tester()
Debug.Print Format$( _
ThisWorkbook.BuiltinDocumentProperties("Revision Number"), _
"000000")
End Sub

This will effectively drop the integer in question into the immediate window. Also, you can see the number in Statistics Group of the Workbook's Properties.

[roos01]

Hi Dave,
Nice job you did!
perhaps adding this line in you code to set the macro is making it more fancier since you would never know if the format is set on forehand.




Sheets(1).Range("a1").NumberFormat = "000000"

Cheers!
Jeroen

[brettdj]

Hi Dave,
Nice job you did!
perhaps adding this line in you code to set the macro is making it more fancier since you would never know if the format is set on forehand.



Sheets(1).Range("a1").NumberFormat = "000000"

Cheers!
Jeroen

Great to see you here Jeroen :)

Yep, I was lazy..should have included that

Cheers

Dave

[roos01]

Glad to be here.
but the main reason is offcourse to beat you with snake:)
but this will be hard to do

[byundt]

Yet another way to store the value is in a named constant. This could be used in any cell without need to change the macro--perhaps a little more flexible than the code previously posted. In honor of the questioner, let's call this constant DreamboatCounter. You would use it on a worksheet with a formula like:
=DreamboatCounter

The code to create it and update it is a Workbook_Open sub stored in the ThisWorkbook code pane:


Private Sub Workbook_Open()
If IsError([DreamboatCounter]) Then
ThisWorkbook.Names.Add Name:="DreamboatCounter", RefersTo:="=TEXT(1,""00000#"")", Visible:=False
Else
ThisWorkbook.Names("DreamboatCounter").RefersTo = "=TEXT(" & [DreamboatCounter] + 1 & ",""00000#"")"
End If
End Sub

[JackInTheUK]

Anne

Few bits :

OK we seem to want to produce an invoicing system that fairly automated in a few ways, say Invoice number [increase automatically] date, address blar blar, I design and develop a few of these and many financial documents for friends and now clients I have some are extremely good even if to me a little chunky and over basic but that?s what they want, so im happy.

My call would be this :

Use and XLS not XLT, sure we can code in an XLT and save out as XLS with ease just my IMO stay with XLS.
To save the new doc is OK I would strongly looks at a way to uniquely save as the XLS each time a new invoice is produce this has caught many guy out as administration of the save as is complex, however there are fixes to this which hop around that and cause no problems.
The save to location can be built to make the directory and dump all in there this will nead hard code as possible network locations or maybe use a xls sheet called myData for dumping all the sensitive date such as last number, directory location rather that this workbook path which will only ever cause problems, the aim is no matter where you run the XLS from the save as dump to the correct X location.

The open statement to trigger is fine or can be on form / toolbox command button, I would attach strong error handling on before close to cross check the increment have increased and error trap = 1 no more or less and that the file has been save else message box the error and abort save unless click OK but the user, so all abort.

As a project like these its forever under re-view and development so can be added to at anytime so the structure and layout need planning, I have a larger project I have recently update covering lots or areas, RoyUK and Jacks web site www.excel-it.com will have loads of invoicing and finance works there for free and corporate downloads, even offering free lessons and so on ?

IF your interested please email me and ill be so very glad to dig deep and send over what I have that?s due for our web site before this months out, in short you get it first

If you want drop Jack a line and we can chew over stuff if J & R can help you more ? just let us know.

Jack

BTW I would very strongly look into the save as file name to be automated and linked / cross referenced to the invoice number as a very first job long before the design, that a promise I have loads of guys fall down here.

EDIT: gess re-read that sounds a wofferley don?t mean to be just offering advice from stuff I do, wait till you see our work, be very pleasantly surprised.

[jamescol]

I'm not sure we're answering the right question, based on how I read the problem. It sounded to me like we want create a counter to capture the number of times a user opens (and presumably uses) a (centralized?) template - not the number of times the subsequent XLS is opened.

DB - Can you clarify?

[byundt]

If so, we're going to need to save a copy of the file back to the original template to capture the newly incremented serial number. This step could be taken in the Workbook_Open sub, right after the incrementation.

[Anne Troy]

Yes. This is something I'd want to use for invoicing, much like MyInvoicing at www.theofficeexperts.com/downloads.htm (http://www.theofficeexperts.com/downloads.htm)

But I just want the autonumber thing. To create invoices or purchase orders. We discussed the same for Word. I think we've decided we'd like TWO KB entries for each Word and Excel: one to autonumber for a single user, one for multiple users.

And, as usual, we just want the best methods; but I don't think it means we have to struggle over them. :)

Anyway, we're away, and on dialup (sucks!!), but I didn't want to ignore the questions. LOVE seeing you post, Brad. Jack: I didn't read yours 'cause I don't have enough time tonight, but will read them as soon as we get home tomorrow.

[JackInTheUK]

byundt Administrator
If so, we're going to need to save a copy of the file back to the original template to capture the newly incremented serial number.
>>> Not so sire why that will be from an accounting point or view you will need to produce copies not re make copy?s / originals, and not forget pro forma?s, so there is no need to edit the template, this I have mentioned in previous post and was about the work in xls and save as unique file name xls so problem solved, this is a routine issue for me and I do this every day, thus the suggestion.

This step could be taken in the Workbook_Open sub, right after the incrementation.
>>> Not sure this is so good as once its changed you will have techi issues or need manual intervention to go back, the change is only to happen on SAVE an record back to the template, not before else you loose numbers in the mix if you abort cancel or not use, that?s extremely poor accounting and auditors will hate that one, an become questioning. There really is no need to open, before close statements for this is should be on command.

jamescol
Problem clarification
I'm not sure we're answering the right question, based on how I read the problem.
>>> Sure no problem just each interpretation ? Jack has offered a lot more and development ideas, as I said I design loads of these , sorry if not received with the heart of help was ment to be sent with, my apologies.

It sounded to me like we want create a counter to capture the number of times a user opens (and presumably uses) a (centralized?) template - not the number of times the subsequent XLS is opened.
>>> Erm I do not thing I make any reference to that ? saying open, on command buttons, just the same could be a icon in the any of the toolbars, just the same.

DB - Can you clarify?
>> ?? Question directed to DB

Dreamboat
But I just want the autonumber thing. To create invoices or purchase orders.
>>> This is where things could go wrong if case is not taken, its not a simple case of one code for two sheets to trigger what you want, this again goes back to Jacks strong recommendation to have the docs / now sheets saved correctly ? this can be messy as Word is better for letters but Excel will rule for invoices, might be worth looking at exporting to word, I know that?s not what you want ideally but offers an option, the reason I say is you now have invoice and purchase these are very different documents, credits a debits could be classed the same pro formas an purchase are not, one is sales ledger whilst the other is purchase ledger I would again from a professional account point suggest never mix the two so I guess im saying use two xls files for this ?.

We discussed the same for Word. I think we've decided we'd like TWO KB entries for each Word and Excel: one to autonumber for a single user, one for multiple users.
>> ?.

And, as usual, we just want the best methods; but I don't think it means we have to struggle over them.
>>> ? I don?t know what?s best .. Jack can only offer what I do an is used ? that?s all, I have bee in finance for 20 years and designed loads of stuff ? o I?m just passing on what I know and have learnt, sure I have popped in a few pitfalls ? just hope they are received in the manor that are ment as just help, not a negative.


Anyway, we're away, and on dialup (sucks!!),
>> OI Anne no that al Jack has.. does ok,, still gets on line that all that counts !! LOL !!!

but I didn't want to ignore the questions. LOVE seeing you post, Brad. Jack: I didn't read yours 'cause I don't have enough time tonight, but will read them as soon as we get home tomorrow.
>> no problems Anne just a polite offer ? its up to you really, just as I say when guys see my work you be amased? think what ever anyone wants Jack have nothing to prove my work [our work with RoyUK] will prove all I say, don?t take my word for it check the web site?. Your be checking back ?.


Have fun guys.

Jack

[NateO]

But I just want the autonumber thing. To create invoices or purchase orders. We discussed the same for Word. I think we've decided we'd like TWO KB entries for each Word and Excel: one to autonumber for a single user, one for multiple users. FYI, I'm confused as to the difference. Why does the procedure care about how many people use the file? :dunno

[Anne Troy]

I think, Nate, that people are wondering where to store the last used number. It may be fine to do it in the template for one user. But if we have multiple users, we may just want a text file on a server or something.

[NateO]

You're going to have portability issues with the external file storage approach. It seems to me that it would be best to house the number with the workbook one way or another.

Also, it turns out that Shared workbooks support Document Property modification via VBA.

[brettdj]

Hi DB,

My earlier post did store a number for each user logon. Code reposted with a mod now to display the current logged on user in A1 and the times they have opened this file in B1.

Cheers

Dave



Private Sub Workbook_Open()
Dim CurUser As String
CurUser = Environ("Username")
With ThisWorkbook
.Sheets(1).Range("A1").Value = CurUser
.Sheets(1).Range("B1").Value = GetSetting("Counter", "Value", CurUser & " " & .Name, 1)
SaveSetting "Counter", "Value", CurUser & " " & .Name, Sheets(1).Range("B1").Value + 1
End With
End Sub

[byundt]

We have several ways to store a value, but have not tied it all together with the proper functionality as JackInTheUK points out. In order for the code to work properly in a template file, the following must be accomplished:
1) Increment the invoice number when the template file is opened
2) Store the incremented number in a means accessible to the template file if and only if the .xls file based on the template is used.
3) Defeat all of the above code in the .xls file. Once saved, its invoice number should never change again

Point number 1 has been accomplished several different ways. Points 2 and 3 are yet to be done.

To address point 2, I suggest that a static Boolean variable be created and set to True when the template is opened and the invoice number incremented. This will be part of the Workbook_Open sub. This Boolean value would be tested in a Workbook_BeforeSave sub--if True then updated invoice number would be stored back to the registry (GetSetting/SaveSetting method), or the BuiltInDocumentProperties or Defined Name of the original template file. The brute force way to accomplish this would be to open another instance of the template, allow it to autoincrement, then save it as a template in its original location and close it. The more subtle way would be to modify the required information in the closed template file.

Point 3 could be accomplished by wiping out the code in the xls file as part of the Workbook_BeforeSave macro.

[Anne Troy]

Right, Dave. I could care less how many times the file has been opened and by who (whom?). It's about getting an invoice number. I don't care a whole lot about the user perhaps having to go into the template or some text file to FIX the number if they screw up. This is a simple KB entry for the small business person who MAY use it on one PC, but could use it on 2 or 3 PCs. We don't need a big solution.

[JackInTheUK]

Maybe Anne but as any financial documents you / we / them must be careful, to be blunt how will you go back say you cancel an invoice at the last moment. or produce it and then its void, what then, no power to edit or go back, also no mention of a credit note. Just see that most users can and will do the unthinkable and in time if lots of numbers missing how will anyone know what or where they are, you wont even be able to the accounting bookwork, that?s bad practice.

I understand the need / want for kb entry, thats fine, just pointing out knowledge i am involved at every single day and the trouble Jack getting involved, its ver valuable knowledge im giving to any business, - Jack had his own business in construction few years back for some time, gave that up to dirty for a man in suites, the Tax people pulled me to their head office to question me - Jack was untouchable.... thats a fact and one i pride myself on. My paperwork was perfection no matter what i was asked for i could produce in electronic and paper version, and noting was out of place even teh cash match the books perfectly, kind of unknown to Tax man [IRS]

I know just a small code procedure is wanted, but my knowledge and heart tells that it?s a solution not a few code lines that are require and the understanding of how to develop and instigate this is paramount, IMO, that?s all im saying, nothing more or less, not working an angle to be difficult, like I saye just from the heart.

Jack

[Anne Troy]

Well, I say it's as simple as including the instructions about going into that text file or into the template (where ever we store that invoice number) and taking it back down one if they want. Some may not even care, and just want a unique invoice number to provide as a reference.

I'm trying to keep this simple. If they want something extravagant, hell...they can go buy MS Money or Quicken, right? So, you see, this has just been all blown out of proportion. I just want a simple invoice number. I've already got a file that does it (MyInvoicing, which I referred to earlier), and I'll ask Zack to remove all the other junk out of it. :)

No offense to anyone at all, it's just taking up far too much of everyone's time for what I wanted. But...there's always that learning they, hey?

[Anne Troy]

I guess this one was never added to the KB. :mkay

[johnske]

Hi Anne,

You mean something simple like this? Or something more?



Option Explicit
Private Sub Workbook_Open()
With Sheets("Sheet1")
[A1] = [A1] + 1
End With
End Sub

[Anne Troy]

No...we'd need it to keep track, like invoice numbers.

I think there should be an entry each for single users and multiple users.

[johnske]

No...we'd need it to keep track, like invoice numbers.

I think there should be an entry each for single users and multiple users.


So a single workbook is going to be shared? How are you to determine who's using it - password protection?

PS: Will change my previous code to give protection for the number of times it's opened >>[vba]Option Explicit


Private Sub Workbook_Open()
With Sheets("Sheet1")
.Unprotect
Cells.Locked = False
[A1] = [A1] + 1
[A1].Locked = True
.Protect
End With
End Sub

[Anne Troy]

Hi, John. Did you read this whole thread?
I think this is why it never got written up. Everybody is making it more difficult than necessary. I think what we need is this...

1. A place to store the last invoice (auto) number. Perhaps a TXT file that sits on the user's PC or on the server.

2. When we open the Excel file (or XLT?), we increment that number by one, and change it in the TXT, too.

If somebody goofs, well, then they can learn how to edit the TXT file.

Here's a perfect example, and I don't think it even uses a text file, but I think it's also only good for one person (but who cares? it's better than NO kb entry for this, and we can make a separate one for multi-users). Feel free to download it and steal the code. If it's password protected, don't crack it, just ask me for the password. :)

http://www.theofficeexperts.com/downloads.htm#ExcelDownloads

[johnske]

Hi Anne,

Part of what I'm trying to find out is: Is this invoice going to be printed (or maybe emailed)? - If so, it'd probably be better to do the count update on the printout command rather than on the Workbook_Open. i.e. the count would only be incremented if the order went ahead to the point of having an invoice printed for it...

I'm assuming you're going to have something like Invoice Number 00123 somewhere and, Served By: Joe Bloggs somewhere else.

If the workbook's unprotected (or even if not) you could have a small userform with command buttons (with your reps. name on them) pop-up on Open, they click their name and it goes on the invoice - with the invoice count for that person and they just enter the details.

All this would not be hard, but at the same time it would take more than two or three lines of code...

Regards,
John

[Anne Troy]

There is no "joe bloggs", John. This has nothing to do with designating a user. I only mentioned multi-users so that more than one person can do invoices.

Why don't we just forget the multi-user thing for now.

I want to create invoices, and have the number increment by one. I don't care HOW that is done. :)

[johnske]

Hi Anne,

Using your example (in the link) - this increments by one every time an invoice has been printed.

Regards,
John

EDIT: Temp attachment has now been removed (John)
Reason: Read next post + only done for one sheet (didn't realize there was more than one "Invoice" sheet)

[Anne Troy]

Sorry. I prolly wasn't real clear about that. I sold that site to MrExcel, so we can't just go take the whole file. Any way we can clear all the stuff out and just have the top sheet, with no data tracking? Then, just put it in the KB, not here. :)

LOL. You might wanna remove www.TheOfficeExperts.com (http://www.TheOfficeExperts.com) from the properties, too. And put YOUR name in there instead of mine.

[johnske]

I only did it as an example. Gotta go out now - later...
John

[Anne Troy]

Oh. Sorry!! :doh: :omg2: :blush :o:

[johnske]

....Any way we can clear all the stuff out and just have the top sheet, with no data tracking? Then, just put it in the KB, not here. :)

LOL. You might wanna remove www.TheOfficeExperts.com (http://www.theofficeexperts.com/) from the properties, too. And put YOUR name in there instead of mine.

Done - with data tracking :yes

[lucas]

While reading this I realized I had seen this question before. I got this from Aaron T. Blood at www.XL-Logic.com (http://www.XL-Logic.com) a while back. Seems to work. It uses a textfile and it increments each time the file is opened, whether you save it or not.


Sub Auto_open()
On Error GoTo ErrorHandler
One:
Open "c:\Counter.txt" For Input As #1
Input #1, x
Close #1
x = x + 1
Two:
Sheets(1).Range("A1").Value = x
Open "c:\Counter.txt" For Output As #1
Write #1, x
Close #1
Exit Sub
ErrorHandler:
Select Case Err.Number
Case 53 'If Counter file does not exist...
x = InputBox("Enter Number to Begin Counting With", "Create 'Counter.txt' File")
Resume Two
Case Else
Resume Next
End Select
End Sub

[nourfarsi]

Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Sheets("sheet1")
Dim lr As Long
lr = ws.Range("a65536").End(xlUp).Row
With Sheets("Sheet1")
If IsEmpty(.Range("A1")) Then
.Range("A1").Value = 0
Else:
ws.Cells(lr + 1, 1) = Val(ws.Cells(lr, 1)) + 1
End If
End With
:banghead: :banghead: :banghead: