Solved

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

Posted on 2012-04-09
15
363 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:kaceyjames
  • 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 41

Expert Comment

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

Worksheets("Sheet1").Range("A1").Value = Now
0
 
LVL 80

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 41

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 80

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 80

Accepted Solution

by:
byundt earned 500 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:kaceyjames
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:kaceyjames
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 41

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 80

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 80

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 80

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 80

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:kaceyjames
ID: 37893591
Worked great.
Thank you
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

My experience with Windows 10 over a one year period and suggestions for smooth operation
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now