Macro to check when another spreadsheet was last modified

[Abuba3000]

Hi guys,

I was wondering if it is possible for excel to check when another spreadsheet was last modified.

I'm building a macro that gathers information from several different workbooks and condenses it to one report. One of the workbooks has two macro's in there (clearinfo and gatherinfo).

What I would like in an ideal world is the macro to do the following:

1. Open file from network drive
2. check when file was last modified
3. create a dialog box saying "filename was last modified ## days ago, do you want to update data?" with a yes or no option.
4. If the user hits yes then it should run clearinfo then run gatherinfo in that workbook. Then save the results.
5. If the user hits no then it should end the sub.

If it's not possible to check the modified date, then just having the dialog box saying "Do you want to update data?" will do.

Hope I made that clear.

 

[tlowry]

I think this is what you asked for:

Sub test()
    main001 ("c:\temp\Main00.xls")
End Sub
Sub main001(sFile)
    Select Case Dir(sFile)
        Case ""
            MsgBox "There is no file: " & sFile
        Case Else
            Select Case MsgBox(Chr$(34) & sFile & Chr$(34) & vbCrLf & vbCrLf _
                        & "Was last modified: " & DateDiff("d", GetDateLastModified(sFile), Now) _
                        & " Days ago." & vbCrLf & vbCrLf _
                        & "Do you wish to use this file?", _
                        vbYesNo, "File modification")
            Case vbYes
                With Workbooks.Open(sFile)
                    clearinfo
                    gatherinfo
                    .Save
                    .Close
                End With
            Case Else
        End Select
    End Select
End Sub
Sub clearinfo()
    ' your code
    MsgBox "clearinfo on " & ActiveWorkbook.Name    ' remove this
End Sub
Sub gatherinfo()
    ' your code
    MsgBox "gatherinfo on " & ActiveWorkbook.Name   ' remove this
End Sub
Function GetDateLastModified(filespec) As Date
    Dim fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFile(filespec)
    GetDateLastModified = f.DateLastmodified
End Function

 

[Abuba3000]

Hi tlowry,

Thanks for this, I'll be testing it over the weekend. The two info macros are already in the second spreadsheet, is it possible to just run those from this one or do I need to insert that coding into this macro?