Last modification date of open workbook

Question

Vba newbie. Need a function to output the last modification date of an open workbook. Here is what I have so far but I am getting a message that my formula contains an error when I invoke the function:

Function LastWBModDate(wbname)

    ActivateWB (wbname)
    LastWBModDate = Format(FileDateTime(ActiveWorkbook.FullName), "m/d/yy h:n ampm")

End Function

Public Function ActivateWB(wbname As String)

    If IsWBOpen(wbname) Then
        Workbooks(wbname).Activate
    Else
        MsgBox "Workbook : " & wbname & " is not open " & vbNewLine
    End If

End Function

Public Function IsWBOpen(wbname As String) As Boolean

    On Error Resume Next
    If Workbooks(wbname) Is Nothing Then
        IsWBOpen = False
    Else
        IsWBOpen = True
    End If

End Function

Thanks!

How are you calling this function? From a worksheet, or from other VBA code? A function called from a worksheet is restricted in what it can do: it cannot for example cause a different workbook to be activated. — Tim Williams, May 20 '13 at 20:26
Good to know @TimWilliams. I was attempting to call the function from a worksheet. — lajulajay, May 20 '13 at 21:00

score 6 · Accepted Answer · answered May 20 '13 at 21:15

6

Function LastWBModDate(wbname As String)
Dim rv, wb As Workbook

    rv = "workbook?" 'default return value

    On Error Resume Next
    Set wb = Workbooks(wbname)
    On Error GoTo 0

    If Not wb Is Nothing Then
        rv = Format(FileDateTime(wb.FullName), "m/d/yy h:n ampm")
    End If

    LastWBModDate = rv

End Function

answered May 20 '13 at 21:15

Tim Williams

154,628
8
97
125

I'm curious why the formatting is needed. Doesn't FileDateTime return a DateTime datatype? Why not just return this datatype (or cvErr(xlErrNA)) if wb is Nothing. This way, you can do easy math with the return type in Excel and make use of Excel's native IfError and IsError functions (as opposed to having to convert a string to a date just to do math, and manage the instances where "workbook?" is returned, all of which causes unnecessary process overhead which, while probably unnoticeable at this scale, is still waste given the single-threaded nature of UDFs). – William Aug 05 '16 at 22:01
You can implement this however you prefer - it's just an example, so I mirrored what the OP was using. – Tim Williams Aug 05 '16 at 22:02

Santosh · Answer 2 · 2013-05-20T20:07:56.197

4

Try below code :

You may also refer this link

Put below code on ThisWorkbook code section

Private Sub Workbook_Open()
    LastWBModDate
End Sub

Put this code in any Standard Module

Function LastWBModDate() As String

    Dim FSO As Object
    Dim File As Object

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set File = FSO.GetFile(ThisWorkbook.FullName)
    LastWBModDate = Format(File.DateLastModified, "m/d/yy h:n ampm")
    Msgbox LastWBModDate 
    Set FSO = Nothing
End Function

edited May 20 '13 at 20:07

answered May 20 '13 at 19:55

Santosh

12,175
4
41
72

Thanks for the quick response but there are some constraints I should be explicit about: the workbook whose modification time I need is neither ThisWorkbook nor ActiveWorkbook. Also this is intended to work cross platform (including mounted drives) and I'd rather not deal with file paths because I know the workbook in question will be open when the formula is invoked. – lajulajay May 20 '13 at 20:10
@lajulajay Specify your path `Set File = FSO.GetFile( )` and if exist and is accessible this function would work. – Santosh May 20 '13 at 20:14
1

Not on Mac (assuming this is what's meant by cross-platform) – Tim Williams May 20 '13 at 20:19
Yes, this needs to work on Mac, hence the desire for a solution that does not rely on paths. – lajulajay May 20 '13 at 21:02

Last modification date of open workbook

2 Answers2