Finding the last modified date of an excel sheet

[jackCell]

I would like to find out the last modified date for each excel sheet inside my excel workbook.
Searched online for a long time, but they only have solutions to find the last modified date for the excel workbook.

Cheers,
Jack

[Logit]

From my searching it appears Excel doesn't have the ability to tell you when a sheet was last modified/saved. There is a workaround however :

https://www.extendoffice.com/documen...er-header.html

If you learn of another method, please let us know.

[jackCell]

Saw this online. But not the exact result I wanted. I need the last modified Date to be stored as a Variable for comparison

[Aussiebear]

Perhaps you need to store the last modified date as a variable to search by

[jackCell]

Exactly how I need it to be. Tried modifying the code but only managed to obtain the current date time but not the last modified date

[Logit]

.
If you paste this into the sheet level module :

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As String
    'Log the change date/time
    Me.Range("A1").Value = "Last saved: " & Format(Date, "mm-dd-yy") & " " & Time
    
    'create a variable
    x = Me.Range("A1").Value
    
    'Now test the variable
    Me.Range("G15").Value = x
End Sub

Maybe you can modify it to be used in the ThisWorkbook module so it will apply to all sheets if that is your goal.

[snb]

Or

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  If Sh.CustomProperties.Count = 0 Then Sh.CustomProperties.Add "snb", Date
  Sh.CustomProperties(1).Value = Date
End Sub

[Paul_Hossler]

You could use the _Change event and store the modification date/time in a Name = the sheet name

There are a few differences between what's allowed as a Name name and as a Worksheet name, and there's no automatic cleanup if you delete or rename a worksheet

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim sName As String
    
    sName = "MOD_" & FixName(Sh.Name)
    
    Call ThisWorkbook.Names.Add(sName, "=" & DateTimeToText)
End Sub

and

Option Explicit
Sub test()
    Dim N As Excel.Name
    
    For Each N In ThisWorkbook.Names
        If Left(N.Name, 4) = "MOD_" Then
            If Len(N.RefersTo) = 15 Then
                MsgBox N.Name & " modified " & TextToDateTime(N.RefersTo)
            End If
        End If
    Next
End Sub


Sub test2()
    Dim D As Date
    
    D = TextToDateTime(ThisWorkbook.Names("MOD_Sheet2").RefersTo)
    MsgBox D
End Sub
 
'replaces NL, CR, spaces with underscores
'replaces more that one underscore with only one
'cleans it up
'if the name does NOT start with a letter, prefixes an underscore
Function FixName(s As String) As String
    Dim s1 As String
    
    s1 = Trim(s)
    
    With Application.WorksheetFunction
        s1 = .Substitute(s1, Chr(10), "_")
        s1 = .Substitute(s1, Chr(13), "_")
        s1 = .Substitute(s1, " ", "_")
    
        While InStr(s1, "__") > 0
            s1 = .Substitute(s1, "__", "_")
        Wend
    
        s1 = .Clean(s1)
    End With
    
    If Left(s1, 1) < "A" Or Left(s1, 1) > "z" Then s1 = "_" & s1
    
    FixName = s1
End Function
 
Function DateTimeToText(Optional D As Date = 0) As String
    Dim d1 As Date
    
    If d1 = 0 Then d1 = Now
    DateTimeToText = Format(d1, "yyyymmddhhnnss")
End Function

Function TextToDateTime(s As String) As Date
    Dim s1 As String
    
    If Left(s, 1) = "=" Then
        s1 = Right(s, Len(s) - 1)
    Else
        s1 = s
    End If
    
    TextToDateTime = DateSerial(CLng(Mid(s1, 1, 4)), CLng(Mid(s1, 5, 2)), CLng(Mid(s1, 7, 2))) + _
                TimeSerial(CLng(Mid(s1, 9, 2)), CLng(Mid(s1, 11, 2)), CLng(Mid(s1, 13, 2)))
End Function

[georgiboy]

Is it slow to store these variables in an ini file the same way the language AutoIt does?

just a thought although it would probably be a bit klunky.

[georgiboy]

Here is an example of what i mean:

If you paste this into the sheet module of the sheet you would like the time to be recorded for:

Private Sub Worksheet_Change(ByVal Target As Range)

    x = WriteINI(Me.Name, "Last update", Now())
    y = WriteINI(Me.Name, "Who updated", Environ("Username"))
End Sub

Then paste this into a standard module and change the location of the ini file to suit:

Private Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As LongPrivate Declare Function GetPrivateProfileInt Lib "kernel32" Alias "GetPrivateProfileIntA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal nDefault As Long, ByVal lpFileName As String) As Long
Private Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As String, ByVal lpString As String, ByVal lpFileName As String) As Long
Global str As String
Global nm As String


Private Const conFig = "C:\Users\A\Desktop\Test.ini" ' location of ini file


Public Function GetINIString(ByVal sApp As String, ByVal sKey As String) As String
   Dim sBuf As String * 256
   Dim lBuf As Long
   lBuf = GetPrivateProfileString(sApp, sKey, "", sBuf, Len(sBuf), conFig)
   GetINIString = Left$(sBuf, lBuf)
End Function


Public Function WriteINI(ByVal sApp As String, ByVal sKey As String, ByVal sValue As String) As String
   WritePrivateProfileString sApp, sKey, sValue, conFig
End Function


Sub GetResults()
    Dim ws As Worksheet, x As Date, y As String
    
    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        x = GetINIString(ws.Name, "Last update")
        y = GetINIString(ws.Name, "Who updated")
        If y <> "" Then
            MsgBox "Worksheet: " & ws.Name & vbNewLine & "Last updated by: " & y & vbNewLine & "At: " & x
        End If
    Next ws
End Sub

Make some changes in the spreadsheet and then run the sub "GetResults" and see what you get.

Hope this helps

[Logit]

.
georgiboy

On my machine, I had to change the sheet module macro from Private Sub Worksheet_Change(ByVal Target As Range) ... to ... Private Sub Worksheet_SelectionChange(ByVal Target As Range).

Otherwise the mouse cursor continually flickers.

[georgiboy]

How strange, i don't seem to get that.

Running Windows 7 Pro and Excel 2007

I know... i'm a bit stuck in the past

[snb]

That's why http://www.vbaexpress.com/forum/show...l=1#post375728

[georgiboy]

Sorry i may be a little on the simple side but to me that doesn't explain.

Without meaning to sound rude: Why would this happen? in words

[Logit]

.
Win 10 / 64 bit Excel 2007

[SamT]

I would like to find out the last modified date for each excel sheet inside my excel workbook.

That information is not inherently available. You must create a method of storing that info before you can use it.