Hi All,
My boss has asked me to create some code to check if some workbooks have been updated (not too hard). But he's also asked me to check if the sheets within those workbooks have been changed (this I don't know).
The code I have written so far
Sub MonitorWorkbooks()
' Set our Objects
Set Wb1 = ThisWorkbook
Set Sh = Wb1.Sheets(1)
Application.DisplayAlerts = False ' turn off can't find file alert
Application.ScreenUpdating = False ' turn off screenupdating
' Saved or not Loop
Lastrow = Sh.Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
'Paste Formula Down
Sh.Range("C2:C" & Lastrow).Formula = "=""\""&A2&""\""& B2&""\""&B2&"".xlsx""" ' past down formula to find location of file
For k = 2 To Lastrow 'loop to store the name of where the file is located
NamesLocation(k) = Sh.Range("C" & k).Value
SavedLocation = "N:\Salesforce Guidance & SPC\" & NamesLocation(k)
LastSaved = FileDateTime(SavedLocation)
Sh.Range("D" & k).Value = LastSaved
' Check to see if it has changed
Set Wb2 = Workbooks.Open(Filename:=SavedLocation) ' open the workbook
For Each Sheets In Wb2.Sheets ' loop between sheets to find if they have been changed
'needs something here to check if sheet has been changed since last login
Debug.Print Sheets.Name
Next Sheets
Wb2.Close savechanges:=False ' close the workbook
If Err.Number <> 0 Then
Sh.Range("D" & k).Value = "File Not Present"
End If
Next k
On Error GoTo 0 ' reset error catching
'k = 1 / 0 ' cause an error
Application.DisplayAlerts = True ' turn off can't find file alert
Application.ScreenUpdating = True ' turn off screenupdating
End Sub
Display More
So my code to check workbooks is easily done.
I've setup the structure for opening the workbooks, and I've setup looping between the sheets in the workbook. But I'm lost on what to do from there ('needs something here to check if sheet has been changed since last login).
Maybe I don't even need to open the workbooks?
I have a quick easy solution in my mind, which is to have a close and save date in each sheet from those foreign workbooks. Then my code just needs to find that date and grab it.
But perhaps there is an easier way?
Thanks all