Excel AutoNumber Files

[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

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?

[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.