Notify me if linked workbooks have not been updated

hargreaves1

New Member
Joined
Jul 23, 2018
Messages
2
Hi guys

I have several granular data sheets all pointing to and dynamically updating a single summary sheet/dashboard.
What I would like to add to this is some form of way of notifying me if any of the linking sheets have not been updated for the current day.

Is this possible and if so how?

Thanks!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Assuming it is being updated with formulas directly linking to the individual source workbooks, the following VBA solution will check those individual sources and provide a status.

Code:
Public Sub CheckSourcesUpdated()
Dim LS As Variant
Dim msgStatus As String


    For Each LS In ThisWorkbook.LinkSources
        If FileDateTime(LS) >= Date Then
            msgStatus = msgStatus & Mid(LS, InStrRev(LS, "\"), Len(LS)) & ": UPDATED" & vbCrLf
        Else
            msgStatus = msgStatus & Mid(LS, InStrRev(LS, "\"), Len(LS)) & ": ERR- " & Format(FileDateTime(LS), "m/d/yy hh:nn") & vbCrLf
        End If
    Next LS
    
    MsgBox msgStatus, vbOKOnly + vbInformation, "Status of Linked Sources"
End Sub
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,250
Messages
5,527,635
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top