Office Pro 2003, Excel question

I create a document each week with a template form I created. I want to automatically have the date inserted into a field when I create the weekly form, but and here is the kicker, I only want the date that the form was created on. What happens now if I open the form at a later date the current date changes the original date. So I am trying to figure out how after the original is created the formula in the field can be changed to show just the original date.

 

You could use a macro in a control workbook.

Code:

Sub CreateNewWorkbook()
    Dim NewWorkbook As Workbook
    Set NewWorkbook = Workbooks.Add( "Template.xls ")
    NewWorkbook.Sheets(1).Cells(3, 2).Value = Date
End Sub

This puts the current date in Sheet 1, row 3, column 2. As the script is run at workbook creation, that date is the creation date.

This is some help from the Office 2000 Excel VBA help that explains using Workbooks.Add with a template option:

 

My template is "expense.xlt ". I usually submit my expenses each Monday. When I open the expense.xlt I would type in the date and my name at the top of the form in the proper fields. What I did was to enter "today()" in the field for the date and my name in the field for it and saved the template. Now when I open it I get what I want until I open the "xls" file that was created. When I open the xls file the date changes to the current date, I do not want that to happen. I read the code ReggieB provided, where would I put that in my template "expense.xlt "?

 

Have a look at this tutorial. Code input is shown in step 3.

 

Oh! and change the line

Code:

Set NewWorkbook = Workbooks.Add( "Template.xls ")

to

Code:

Set NewWorkbook = Workbooks.Add( "expense.xlt ")

 

OK, I guess I am missing something. I can create a module and procedures, but how do I get the template to execute the procedure when I load "expense.xlt ". When I load "expense.xlt" much of the form is protected and cannot be changed. After I add my expenses for the week and save the form, I am prompted for a "name ".xls, I do not write over the xlt that I created. If I must edit the xlt I can also do that. So now what kicks off the procedure in the xlt to populate the field with the date (today())? I went through the tutorials for awhile but they did not answer this question.

 

With the method I am suggesting, you don't load "expense.xlt" directly. You create a control sheet (call it "control.exe "). You open that, fire off the macro, and that loads a new spreadsheet based on "expense.xlt" with the date entered.

 

Now I am really lost. This is getting complicated.

 

Yes. I think I have overcomplicated things by giving you another option. Perhaps the best answer is Steve R Jones' original one. Make the entry a manual entry rather than using the current date function.