Web Development

General discussion


VB macro that puts "Last modified" date

By noyoki ·
I'm trying to write a macro in VB (it's for Excel) that will give me the last date that the document was saved. I was intending it to run at startup (through Workbook_Open).

The problem I'm running into is that it appears to be saving the document after being run (I think). I'll change the date (on my PC), run the macro, and end up with one date. Close and save. Change the date and run the macro again, but wind up with whatever date that day is.

Sub MyHeader3()
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
ActiveSheet.PageSetup.RightHeader = "Saved date " _
& FileDateTime(ActiveWorkbook.FullName)
End Sub

Do I even need to do this in VB? Is there a better way? He just wants to have the "Last modified" date updated automatically, rather than having to remember to edit it. What am I doing wrong?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

I didn't find a worksheet function for it, but you can use this code:

Private Sub Workbook_Open()
Dim fsFileSystemInformation
Dim fThisWorkbook
Dim sLastModifiedDateTime As String

Set fsFileInformation = CreateObject("Scripting.FileSystemObject")
Set fThisWorkbook = fsFileInformation.GetFile(ThisWorkbook.FullName)
sLastModifiedDateTime = fThisWorkbook.DateLastModified
End Sub

Collapse -

by noyoki In reply to VB macro that puts "Last ...

How do I use that code to put the date somewhere? (Header, in a cell, doesn't matter where...) I'm not a programmer by trade, and what little I do know is in Java and C++...

Collapse -

That is VBA code, you need to put that into a macro. Open the VB Editor, make this a command, and call that command in the Open and Save event handlers, just to be sure. My code that was provded simply sets sLastModifiedDateTime to the correct value; you will need to set the appropriate ranges to have that value.

Related Discussions

Related Forums