Add date to all new files

K

Kristin Broggi

I am working with Excel XP and I am looking for a way to have the current
date (static) display in the footer of all new files I create. I can create a
macro to insert the footer in the date but I cannot figure out how to get it
to run every time I create a new file. In Word, I can name the macro autonew
and I am all set. Any suggestions for Excel?
Thanks
Kristin
 
G

Gord Dibben

Kristin

Open a new workbook. Customize as you wish. File>Save As Type: scroll down
to Excel Template(*.XLT) and select. Name your workbook "BOOK"(no quotes).
Excel will add the .XLT to save as BOOK.XLT.

Store this workbook in the XLSTART folder usually located at........

C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

This will be the default workbook for File>New

Existing workbooks are not affected by these settings.

You can also open a new workbook and delete all but one sheet. Customize as
you wish then save this as SHEET.XLT in XLSTART folder also. It now becomes
the default Insert>Sheet.

More can be found on this in Help under "templates"(no quotes).


Gord Dibben Excel MVP
 
K

Kristin Broggi

Thank you. I have been able to do this but....
I want the XL file "date stamped" If I create it on 11/29/05, I want the
footer to always read 11/29/05. I can't figure out how to do this.
Any other suggestions.
Kristin
 
G

Gord Dibben

Kristin

Place this UDF in a General Module of your BOOK.XLT

Function DocProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuiltinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Place this code in your BOOK.XLT Thisworkbook Module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws.PageSetup
.RightFooter = DocProps("creation date")
End With
Next ws
End Sub

Store BOOK.XLT in your XLSTART folder.

All File>New workbooks will be based upon BOOK.XLT


Gord
 
Top