How do I know when a file has been modified in a VBA macro?

Question

How do I know when a file has been modified in a VBA macro?

Is there a way to view the file in VBA (which is essentially VB6) so that I know when the file has changed? - similar to this only i don't want to know when the file is not being used, only when it is changed.

The answers I found recommended using "FileSystemWatcher" and Win32 API "FindFirstChangeNotification". I can't figure out how to use them, though any idea?

+2

events vba ms-office filesystemwatcher

Robinicks 18 oct. 09 at 22:13

source to share

4 answers

You should consider using the WMI transient event user to view the file as suggested here , but narrowing it down to a specific file instead of a folder

(This assumes you can't just keep track of the Modified Date property of the file.)

+1

stuartd 18 oct. At 22:26

source to share

Take a look here . The page has a sample "Browse Catalog Demo" by Bryan Stafford.

+1

Wim coenen 18 oct. '09 at 22:30

source to share

I take it to vb6, run it, show: ERROR.

0

hack0573 01 Feb At 3:01 am

source to share

Robinicks · Accepted Answer · 2009-10-19T06:25:03+0000

Ok, I put together a solution capable of detecting file system changes in VBA (VB6).

Public objWMIService, colMonitoredEvents, objEventObject

'call this every 1 second to check for changes'
Sub WatchCheck()
On Error GoTo timeout
    If objWMIService Is Nothing Then InitWatch 'one time init'
    Do While True
        Set objEventObject = colMonitoredEvents.NextEvent(1) 
         '1 msec timeout if no events'
        MsgBox "got event"

        Select Case objEventObject.Path_.Class
            Case "__InstanceCreationEvent"
                MsgBox "A new file was just created: " & _
                    objEventObject.TargetInstance.PartComponent
            Case "__InstanceDeletionEvent"
                MsgBox "A file was just deleted: " & _
                    objEventObject.TargetInstance.PartComponent
            Case "__InstanceModificationEvent"
                MsgBox "A file was just modified: " & _
                    objEventObject.TargetInstance.PartComponent
        End Select
    Loop
Exit Sub
timeout:
    If Trim(Err.Source) = "SWbemEventSource" And Trim(Err.Description) = "Timed out" Then
        MsgBox "no events in the last 1 sec"
    Else
        MsgBox "ERROR watching"
    End If
End Sub

Copy and paste this element next to the one above, it will be automatically called to initialize global vars.

Sub InitWatch()
On Error GoTo initerr
    Dim watchSecs As Integer, watchPath As String
    watchSecs = 1 'look so many secs behind'
    watchPath = "c:\\\\scripts" 'look for changes in this dir'

    strComputer = "."
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
        ("SELECT * FROM __InstanceOperationEvent WITHIN " & watchSecs & " WHERE " _
            & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
                & "TargetInstance.GroupComponent= " _
                    & "'Win32_Directory.Name=""c:\\\\scripts""'")

    MsgBox "init done"
Exit Sub
initerr:
    MsgBox "ERROR during init - " & Err.Source & " -- " & Err.Description
End Sub

How do I know when a file has been modified in a VBA macro?

More articles: