Web Development

Ask something
Discussion Locked

VB macro that puts "Last modified" date

0 helpful
3 replies

by noyoki

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

3 replies

by justin james

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

by noyoki

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++...

by justin james

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.

This discussion is closed to new replies.