Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I would like to add a time stamp to Excel and Word

Posted on 2012-04-09
15
Medium Priority
?
434 Views
Last Modified: 2012-04-25
Greetings experts,

I would like to know if there is a way to verify an excel or word file was saved when I click CTL-S without checking windows explore for the time stamp.

So when I click CTL-S, the file name on the top will change from test to test-040912-14:33 or something like that.  Then if I click CTL-S again 5 minutes later it would change to: test-040912-14:38

I'm guessing that a Macro can be created and put into a template for the users to start their workbooks with or something like that?  I found some stuff via Google, but I'm a tech and don't normally work with Excel macros or vbs files, so I would need a step-by-step process.

We are using Excel 2010 and Word 2010.

Any help would be appreciated,
Kacey
0
Comment
Question by:Kacey Fern
  • 7
  • 3
  • 3
  • +1
15 Comments
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37825228
Sure,  this gets current value and pastes it into a cell. let me see....

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Range("'Sheet1'!A1").value = Now()

End Sub 

Open in new window


should do the trick

In VBA editor you would want this on the "This workbook" item.

And you would change Sheet1 to the worksheet you wanted and
A1 to the cell you wanted it in.
0
 
LVL 11

Expert Comment

by:ScriptAddict
ID: 37825268
Ummm step by step:

Make sure you have the developer ribbon enabled:
1)Right click on ribbon in blank area to right side.  
2)Click checkmark for toolbar for Developer in right column
3)Click ok.

Go into VBA and paste code
3)Click on Developer ribbon
4)Click on Visual Basic (far left)
5)Click on "This workbook.
6)Paste code from above.  

Save a few times and watch it update the seconds/minutes etc.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37825709
Not for points, but suggest coding as:

Worksheets("Sheet1").Range("A1").Value = Now
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 81

Expert Comment

by:byundt
ID: 37825793
Kacey,
If you want the actual name of the file to change to include the time stamp, then you might use a File_BeforeSave macro like:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim s As String, sFile As String, sPath As String
s = Me.FullName
Cancel = True
If SaveAsUI = True Then     'Display the file browser
    s = Application.GetSaveAsFilename(Me.Name, "Macro enabled workbook (*.xlsm), *.xlsm")
    If s = "False" Then Exit Sub
End If
sPath = Left(s, InStrRev(s, "\"))
sFile = Mid(s, Len(sPath) + 1)
If Right(LCase(sFile), 18) Like "-###### ##.##.xlsm" Then
    sFile = Left(sFile, Len(sFile) - 18) & Format(Now, "-mmddyy hh.mm") & ".xlsm"
Else
    sFile = Left(sFile, Len(sFile) - 5) & Format(Now, "-mmddyy hh.mm") & ".xlsm"
End If
Application.EnableEvents = False
Me.SaveAs sPath & sFile, FileFormat:=52     'Macro enabled Excel workbook (.xlsm format)
Application.EnableEvents = True
End Sub

Open in new window


You can't have a colon as part of the file name, so I change the time to be hh.mm instead of hh:mm

Brad
Test-040912-18.32.xlsm
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37825805
And then, there's looking at the actual question.  For some reason my eyes focused on syntax instead of scope.

Thanks for stepping in with the solution, Brad!

Dave
0
 
LVL 81

Expert Comment

by:byundt
ID: 37825900
Kacey,
If you just want the caption of the window to reflect the time of the last change (i.e. don't change the file name), then you might use:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim s As String, sCaption As String, sFile As String, sPath As String
s = Me.FullName
sCaption = Me.Windows(1).Caption
Cancel = True
If SaveAsUI = True Then     'Display the file browser
    s = Application.GetSaveAsFilename(Me.Name, "Macro enabled workbook (*.xlsm), *.xlsm")
    If s = "False" Then Exit Sub
End If
sPath = Left(s, InStrRev(s, "\"))
sFile = Mid(s, Len(sPath) + 1)
If Right(LCase(sCaption), 26) Like "last saved at ###### ##:##" Then
    sCaption = Left(sCaption, Len(sCaption) - 18) & " Last saved at " & Format(Now, "mmddyy hh:mm")
Else
    sCaption = sCaption & " Last saved at " & Format(Now, "mmddyy hh:mm")
End If
Application.EnableEvents = False
Application.DisplayAlerts = False
Me.SaveAs sPath & sFile, FileFormat:=52     'Macro enabled Excel workbook (.xlsm format)
Me.Windows(1).Caption = sCaption
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Open in new window


Brad
0
 
LVL 81

Accepted Solution

by:
byundt earned 2000 total points
ID: 37825924
Kacey,
Now that I think about it further, it is much simpler to put a time stamp in the caption of the window:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Me.Windows(1).Caption = Me.Name & "   Last saved on " & Format(Now, "m/d/yy h:mm")
End Sub

Open in new window


Brad
0
 

Author Comment

by:Kacey Fern
ID: 37825933
Pretty slick Brad.  I think this is going to work perfectly for my client.

Thanks ScriptAddict for describing how to work in the developer ribbon.  
Let me check this out on a few computers tomorrow and then I'll close it out.

Thanks again,
Kacey
0
 

Author Comment

by:Kacey Fern
ID: 37825949
Hey Brad,

Am I pushing it to ask what needs to be changed for this to work in Word?  I just pasted the same thing into the VB editor in a word doc, but it didn't work.  I'm assuming it has something to do with the Workbook commands.

Kacey
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37825950
Brad - You'd have to save it at least once to get that caption.  Suggest adding code to the Workbook_Open otherwise, the user would be uninformed until the first save, and I guess he/she'd just need to check the wristwatch at that point.

Dave
0
 
LVL 81

Expert Comment

by:byundt
ID: 37825995
Dave raises a good point. Here is the Workbook_Open sub that you would need (in addition to the Workbook_BeforeSave)

Private Sub Workbook_Open()
On Error Resume Next
Me.Windows(1).Caption = Me.Name & "   Last saved on " & Me.BuiltinDocumentProperties("Last Save Time")
On Error GoTo 0
End Sub

Open in new window



For Word, the equivalent of the Workbook_Open goes in the ThisDocument code pane:
Private Sub Document_Open()
On Error Resume Next
Me.Windows(1).Caption = Me.Name & "    Last saved on " & Me.BuiltInDocumentProperties(wdPropertyTimeLastSaved)
On Error GoTo 0
End Sub

Open in new window


I need to do some more digging to get the equivalent of Workbook_BeforeSave.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 37826040
The relevant instructions for trapping Word's Save event are here:
http://word.mvps.org/FAQs/MacrosVBA/AppClassEvents.htm

My concern is that sending the document to another person (who doesn't have the template) will cause a problem. I don't know the workaround for that.

I've therefore added the Word Zone to this question so those people might get a chance to help.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 37828954
The problem with Word is easier than I thought--if a file recipient doesn't have the template, then they just don't get the benefit of last saved file date being displayed in the caption.

If you put the following code in a macro-enabled template (.dotm), any document made using that template will display the last saved date and time in the window caption. The Word document need not contain any code.

To create the template, use the File...New menu item and choose the option for Template. I called mine DocsWithEvents.dotm

Put all of the following in the ThisDocument code pane of a Word template:
Private WithEvents wdApp As Word.Application

Private Sub Document_New()
If wdApp Is Nothing Then Set wdApp = ThisDocument.Application
End Sub

Private Sub Document_Open()
If wdApp Is Nothing Then Set wdApp = ThisDocument.Application
End Sub


Private Sub wdApp_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
If ActiveDocument.AttachedTemplate <> ThisDocument Then Exit Sub

Doc.Windows(1).Caption = Doc.Name & "    Last saved on " & Format(Now, "m/d/yy h:mm")
End Sub

Private Sub wdApp_DocumentOpen(ByVal Doc As Document)
If ActiveDocument.AttachedTemplate <> ThisDocument Then Exit Sub

Doc.Windows(1).Caption = Doc.Name & "    Last saved on " & Doc.BuiltInDocumentProperties(wdPropertyTimeLastSaved)
End Sub

Open in new window

0
 
LVL 81

Expert Comment

by:byundt
ID: 37830381
Kacey,
Now that I see how Word needs to implement the feature, it occurs to me that Excel can work the same way. If you put the code in an add-in, the Excel workbook doesn't even need to be macro-enabled. As long as the add-in is active, any workbook you open will display the last saved date in its window caption.

In my opinion, this is much better than the previous method suggested--though people you send a workbook to won't be able to enjoy the feature. But neither will its absence cause them any problems.

Put all of the following code in ThisWorkbook code pane of an otherwise blank workbook, then save that workbook as an add-in (file type .xlam). If you then enable the add-in, it will launch whenever Excel does--and give you the desired feature.

To enable the macro, open the Developer...Add-ins menu item, then check the box for the add-in that you just created. You can also do this from the File...Options...Add-ins menu item by using the Go button.
Private WithEvents xlApp As Excel.Application

Private Sub Workbook_New()
If xlApp Is Nothing Then Set xlApp = ThisWorkbook.Application
End Sub

Private Sub Workbook_Open()
If xlApp Is Nothing Then Set xlApp = ThisWorkbook.Application
End Sub

Private Sub xlApp_WorkbookAfterSave(ByVal Wb As Workbook, ByVal Success As Boolean)
On Error Resume Next
If Success = True Then Wb.Windows(1).Caption = Wb.Name & "    Last saved on " & Format(Now, "m/d/yy h:mm")
On Error GoTo 0
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
On Error Resume Next
Wb.Windows(1).Caption = Wb.Name & "    Last saved on " & Wb.BuiltinDocumentProperties("Last Save Time")
On Error GoTo 0
End Sub

Open in new window


Brad
0
 

Author Closing Comment

by:Kacey Fern
ID: 37893591
Worked great.
Thank you
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question