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
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
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.
Saw this online. But not the exact result I wanted. I need the last modified Date to be stored as a Variable for comparison
Perhaps you need to store the last modified date as a variable to search by
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
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
.
If you paste this into the sheet level module :
Maybe you can modify it to be used in the ThisWorkbook module so it will apply to all sheets if that is your goal.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
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
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
Last edited by Paul_Hossler; 01-25-2018 at 10:36 AM. Reason: Keyboard made mistakes
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
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.
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:
Then paste this into a standard module and change the location of the ini file to suit: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
Make some changes in the spreadsheet and then run the sub "GetResults" and see what you get.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
Hope this helps
.
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.
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
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
.
Win 10 / 64 bit Excel 2007
That information is not inherently available. You must create a method of storing that info before you can use it.I would like to find out the last modified date for each excel sheet inside my excel workbook.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ