VBA TUTOR NEWSLETTER ~ TUTORIAL SIXTEEN: CREATING AND USING A DATE/TIME STAMP

>GRSoftware >VBA Tutor >Newsletter Tutorials >Tutorial 15

VBA TUTOR NEWSLETTER ~ TUTORIAL SIXTEEN:

CREATING AND USING A DATE/TIME STAMP

This month we will look at how to use a date/time stamp in both WORD and EXCEL. The use of a date/file stamp to save the document or worksheet with the current date and time as its file name has uses in many office situations.

STEP ONE: Creating the Date/File stamp.

The creation of the file name string is best achieved by using the 'Format' function.

In WORD it looks like .....

ActiveDocument.SaveAs FileName:=Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss")

In EXCEL it looks like .....

ActiveWorkbook.SaveAs FileName:=Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss")

NOTE: In Australia we move logically from the day, to the month, then the year in a date, as you would for the hour, then minute and then the second in the time!
I would really like to know the reason why dates in the US start with the month, then the day then finally the year???

This simple line of code, which should not require any explanation, can be used in a variety of ways, either 'manually' or 'automatically'.

STEP TWO: Applying a Date/Time stamp

The Date/Time stamp code can be applied manually by creating a function that can be called by either a Toolbar button or Menu Item. [ Refer back to tutorial 13 on how to create a ToolBar Button ]

Public Sub DateTimeStampSave()
ActiveWorkbook.SaveAs Filename:=Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss")
End Sub

To apply the Date/Time stamp automatically in a document or template, you can use a 'AutoClose' macro in WORD .....

Public Sub AutoClose()
DateTimeStampSave
End Sub

or the 'BeforeClose' event in EXCEL .....

Public Sub Workbook_BeforeClose(Cancel As Boolean)
DateTimeStampSave
End Sub

Both methods save a copy of the original with the current date and time as the filename, while leaving the original doument or template name intact.

The most obvious variation would be to add a folder name to save to. The code would then look like ...

ActiveWorkbook.SaveAs Filename:="C:\Data\"+Format(Date, "dd-mm-yy") + "_" + Format(Time, "hh-mm-ss")

Using these basics, I am sure you can think of many uses for a 'Date/Time' stamp.

NOTE: NOTE: NOTE: NOTE: NOTE: NOTE: NOTE: NOTE:

You should play around with the date/time formating to see the many possibilities from simple to detailed.

HOPEFULLY, THIS CODE EXAMPLE WILL ADD TO YOUR ABILITY TO BECOME A VBA POWER USER!