Add footer automatically

[bgelbord]

Let me start by saying that I am new to programming VBA Macros, but I'm
learning on the fly and I'll be happy to try out any and all
suggestions. Now, on to the problem.

We're trying to come up with a way of having a unique id number for
every new document created. I think that a good way to do this is to
create templates for word and excel (the two programs we use for
document creation). I'm trying to come up with a macro for the
templates that automatically adds a footer with a trimmed down
time/date stamp upon the first save. By "trimmed down" I mean that
12/04/2006 11:15:32PM would be shortened to 12042006111532. I only
need it to do this the first time a document is saved because if the
time/date updated itself every time the document was used it would
undermine the whole plan for unique document id numbers.

Thanks for your help.
Brett

 

[Nick Hodge]

Brett

If you only have a single worksheet in the template, (mine uses sheet1),
save this workbook_open code in the ThisWorkbook module

Private Sub Workbook_Open()
Dim yy As String
Dim mnth As String
Dim dd As String
Dim hh As String
Dim mm As String
Dim ss As String
Dim FooterString As String
yy = Year(Date)
mnth = Format(Month(Date), "00")
dd = Format(Day(Date), "00")
hh = Format(Hour(Time), "00")
mm = Format(Minute(Time), "00")
ss = Format(Second(Time), "00")
FooterString = mnth & dd & yy & hh & mm & ss
Worksheets("Sheet1").PageSetup.RightFooter = FooterString
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Jezebel]

You don't need any VBA to do that. Just a field:

{ createdate \@ "yyyyMMddHHmmss" }

 

[B.]

Thank you Nick. I tried this out, and the only problem is that I don't
know how to get it to stop updating the time and date every time the
doc is opened and saved. I don't know if that's even possible.