Excel Macro ? - Add Date/Time/Username

Factual Questions
1

I am trying to create a macro that will allow me to show in Cell B1, the last date and time the file was saved and in Cell B2, the username of the last person that saved it. I am not familiar at all with writing macros and after a bit of googling I was able to put together the below. This pulls in the correct date, but not the correct time. Time shows as 12:00 AM. And I would still need to add in the username portion. If too much trouble, the username is not as important as being able to tell when the file was last updated. Any help would be greatly appreciated.

Sub LastSaved()
Dim sLMD As String

On Error Resume Next

sLMD = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
If Err = 440 Then
    Err = 0
    sLMD = "Not Set"
End If
sLMD = Format(sLMD, "dd/mm/yy h:mm")
Range("B1").Value = "Last Saved: " & sLMD

End Sub

EDIT: I know this info can be seen when viewing the filename in the folder it is in, but I highly doubt anyone in my office ever pays attantion to that. Will be more visible to have at the top of the file once opened.

2

What version of Excel are you using?

3

2007

4

This worked for me in 2010. Sorry I don’t have 2007 to try it out in:



Sub LastSaved()

Dim sLMD As String

On Error Resume Next

sLMD = ActiveWorkbook.BuiltinDocumentProperties(12).Value
sLAD = ActiveWorkbook.BuiltinDocumentProperties(7).Value

If Err <> 0 Then
Err = 0
sLMD = "Not Set"
sLAD = "Not Set"
End If

sLMD = Format(sLMD, "dd/mm/yy h:mm")
Range("B1").Value = "Last Saved: " & sLMD
Range("B2").Value = "User: " & sLAD

End Sub

I just noticed I didn’t add “DIM sLAD as STRING”. Apparently 2010 doesn’t need it but 2007 might.

5

I’d recommend a slightly different approach. Create a generic function for any document property, then use that function in the cell instead of using a macro to insert it. That way you can add the function to your Normal template and use it in any document.

So the function would be this:


Public Function DocProperty(propertyName)
    Application.Volatile
    DocProperty = ThisWorkbook.BuiltinDocumentProperties(propertyName)
End Function

Then in cell B1 enter


=DocProperty("Last save time")

I also recommend putting the text "Last Saved: " in a separate cell so you can simply format the cell with the date/time as you desire, but if you want it all in one cell you can use concatenation and the FORMAT function.

6

This is not related to the Excel version. If you have “Option Explicit” at the start of the VBA module, then you must first “dim” any variables you use. It’s good practice, IMO.

7

Thanks Troutman. I’m self-taught so I’m sure I’ve missed a lot of ‘best practice’ lessons over the years. But that makes sense.

8

Another approach would be to use the Workbook_BeforeSave event to put the current date and time and username in that cell every time it’s saved, rather than retrieving it after the fact. Your description mentions user name but none of the code in this thread addresses that and I don’t think you can retrieve that after the fact.

9

To expand on CookingWithGas’s reply, place this in the ThisWorkbook code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Range(“B1”) = Format(Now(), “General Date”)
Range(“B2”) = Application.UserName
End Sub

(Or if you must):
Range(“B1”) = "Last Saved: " & Format(Now(), “General Date”)