Update value in Template

[jsolutions]

Hi - this is an Excel VBA question. I have a template that I want to contain a tracking number. Each time a new file is created based on the template, I want the number to increment. So for example, the first new Excel document based on the template would be document number 1, the next one would be document number 2 - and for simplicity, let's say that I want that value to show in cell A1. (NOTE - document 1 and document 2 have no knowledge of each other. In other words, there will be no way to get the most recent value from the previous document. So, I would like for the template to manage this number.

I have started down several paths including storing the value in an external file - updating and reading it any time that the template is accessed.

I am sure that there is a better way to do this - preferably storing the value in the excel template or VBA code. Any help would be greatly appreciated.

Thanks

Jay

 

[SkipVought]

jsolutions,

When the user opens a template, you could have a Workbook_Open event procedure that

1. incriments the Cell Value
2. save as template with original template name
3. save as workbook with incriment value

The procedure shold only be called if the workbook name is the default workbook open template name. Once the workbook has been renamed with the incriment value, the procedure should not be called from the Workbook_Open event.

BTW, Excel files are usually referred to as workbooks. Word files are usually referred to as documents.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[jsolutions]

Skip - thanks for the reply. This is exactly what I want to do. However, when the user access the template, they are not opening the template directly - they are using File...New and selecting the template. Can I still write to the template?? Is the template file actually opened?? And since I only want to trigger this when the file is first created (not each time the resulting workbook is opened) is there a better way than using the Workbook_Open event procedure?? I know I could test the current file name to see if the procedure is running in the template or in the XLS file, but that seems a little clunky.

Could you give me another push here - your suggestion is right on the money, I am just missing some of the start up steps. Thanks for your patience.

And - you are (of course) absolutely right on the Workbook/Document notation in your post. I just finished teaching a 5 day Java course which always throws off my terminology when I switch gears back into VBA. Thanks.

Jay

 

[SkipVought]

Whenever you open a template, it opens as a workbook (.xls)



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com