PDA

View Full Version : Finding the last modified date of an excel sheet



jackCell
01-25-2018, 01:56 AM
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
01-25-2018, 02:14 AM
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/documents/excel/1801-excel-insert-last-modified-date-in-footer-header.html

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

jackCell
01-25-2018, 02:23 AM
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
01-25-2018, 02:26 AM
Perhaps you need to store the last modified date as a variable to search by

jackCell
01-25-2018, 02:33 AM
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
01-25-2018, 03:10 AM
.
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
01-25-2018, 04:09 AM
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
01-25-2018, 09:44 AM
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
01-25-2018, 10:26 AM
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
01-26-2018, 07:03 AM
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
01-26-2018, 08:20 AM
.
georgiboy (http://www.vbaexpress.com/forum/member.php?15252-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
01-26-2018, 08:56 AM
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
01-26-2018, 09:04 AM
That's why http://www.vbaexpress.com/forum/showthread.php?61850-Finding-the-last-modified-date-of-an-excel-sheet&p=375728&viewfull=1#post375728

georgiboy
01-26-2018, 09:10 AM
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
01-26-2018, 11:14 AM
.
Win 10 / 64 bit Excel 2007

SamT
01-26-2018, 01:12 PM
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.