Thread: Macro to check for file modification and display a pop-up or reload the client file.

Hi Everyone!

Here is my issue:
Location 1 opens book1.xlsx, when its saved, it also generates book2.xlsx in read only in another folder. (a copy of book1)

if someone opens book2.xlsx i need a way (by a macro imbedded in the file) to automatically reload the book2.xlsx file on the clients when book1.xlsx is modified. It has to check the file book1.xlsx every say 5 minutes to see if its modified and if it is, it reloads automatically or displays a pop-up on the clients that have book2.xlsx opened so they can reload it.

I was able to make this part that returns the current date stamp...

Sub test()
    Const strFullName As String = "C:\Book1.xls"
    Const strFullName2 As String = "C:\Book2.xls"
    If FileExists(strFullName) Then
        MsgBox FileLastModified(strFullName)
    Else
        MsgBox "Cannot find the file : " & vbNewLine & strFullName
    End If
     
End Sub

 
Private Function FileExists(fname) As Boolean
    Dim x As String
    x = Dir(fname)
    If x <> "" Then FileExists = True _
Else FileExists = False
End Function
 
Function FileLastModified(strFullFileName As String)
    Dim fs As Object, f As Object, s As String
     
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(strFullFileName)
     
    s = UCase(strFullFileName) & vbCrLf
    s = s & "Last Modified: " & f.DateLastModified
    FileLastModified = s
     
    Set fs = Nothing: Set f = Nothing
     
End Function

But I am at a lost for the rest...

Please advise or point me in the right direction...

Thank You kindly

-Aska