Microsoft Excel macro: Append Filename with Time Stamp

Enterprise users are always boggled by two concerns of documentation – Collaboration & Version Tracking. DocVerse which was recently acquired by Google, offers a great collaboration solution. But Version Tracking is what concerns the users more. Emailing/Printing the wrong version happens just too often. Of course, there is this widely used way of adding the version number to the filename. Works for some. For some, life would be simpler by appending a Time Stamp to the filename.

This post will help you in creating a macro for Microsoft Excel that will automate time-stamping for you. Every time you run this macro, you will be prompted to enter the filename, to which the time stamp will be appended. The file would be saved in a .xls format.

Creating a Macro

• Go to Tools >Macro > Macros (or simply press Alt + F8 )
• Then enter the Macro Name <timestamp>
• Then click Create
• In the VB Macro editor that will open, simply paste the following code:

Sub timestamp()

myFilename = InputBox("Enter filename") & " " & _
Format(Now(), "mm_dd_yyyy hh_mm") & ".xls"
myDir = "D:\Documents and Settings\username\Desktop"
ActiveWorkbook.SaveAs Filename:= _
myDir & "\" & myFilename, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

To Run this Macro

Go to Tools > Macro > Macros (or simply press Alt + F8)

Select ‘timestamp’ and click Run

You will be prompted to enter a filename to which the timestamp will be appended. The file will be saved in a .xls format in the path specified. Change the Directory Path in the above code as required.

Also note that, if the path doesn’t exist, then this macro will give an error and wont save the file

Similarly, a macro for Microsoft Word can also be coded.

To save time, simply add a macro button to your toolbars.

Refer to TechRepublic’s How to create macro buttons in Excel and Word for the same.

Enable Macros by default

Many of you would get a dialogue box to enable or disable the macros every time you open excel. To stop this, make the security levels for the macro as low. Note that this may compromise your security if any malicious macros are installed.

Further Updates

Using an existing filename & just changing the timestamp

Advertisement