Insert current date in Excel template at creation

Question

I'm building an excel template (*.xlt) for a user here, and one of the things I want to do is have it insert the current date when a new document is created (ie, when they double-click the file in windows explorer). How do I do this?

Update: I should have added that I would prefer not to use any vba (macro). If that's the only option, then so be it, but I'd really like to avoid forcing my user to remember to click some 'allow macro content' button.

Answer 1

You can edit the default template for excel -

There is a file called Book.xlt in the XLSTART directory, normally located at "C:\Program Files\Microsoft Office\Office\XLStart\"

You should be able to add a macro called Workbook_Open

Private Sub Workbook_Open()

If ActiveWorkBook.Sheets(1).Range("A1") = "" ActiveWorkBook.Sheets(1).Range("A1") = Now End IfEnd Sub

My vba is a little rusty, but you might find something like this works.

Answer 2

You could use the worksheet function =TODAY(), but obviously this would be updated to the current date whenever the workbook is recalculated.

The only other method I can think of is, as 1729 said, to code the Workbook_Open event:

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Sheet1").Range("A1").Value = Date
End Sub

You can reduce the problem of needing the user to accept macros each time by digitaly signing the template (in VBA IDE Tools | Digital Signature...) and select a digital certificate, however, you will need to get a certificate from a commercial certification authority (see http://msdn.microsoft.com/en-us/library/ms995347.aspx). The user will need to select to always trust this certificate the first time they run the template, but thereafter, they will not be prompted again.

Answer 3

To avoid VBA, and if you think your users might follow instructions, you could ask them to copy the date and then paste special->values to set the date so that it won't change in future.