CODEWITHSUNDEEP
vbaexcel
lajulajay
lajulajay

Reputation: 365

Last modification date of open workbook

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!

Upvotes: 2

Views: 14467

Answers (2)

Tim Williams
Tim Williams

Reputation: 166156

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

Upvotes: 6

Santosh
Santosh

Reputation: 12353

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

Upvotes: 4

Related Questions