I'm having a challenge with the following code "Sub watcher_Created..." The error message is: User-defined type not defined. My OS is Windows 11 64 bit.
I understand that System.IO should be selected in the VBA Project Tools>References. I don't see that option but the closest reference I see is "System". Set ref = ThisWorkbook.VBProject.References.AddFromFile("C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.tlb")
'This code in a class module
Private WithEvents watcher As FileSystemWatcher
'This code in a standard module
Sub MonitorDirectory()
Dim path As String
path = "C:\Temp\DataFiles\"
Set watcher = New FileSystemWatcher
watcher.path = path
watcher.NotifyFilter = (NotifyFilters.fileName Or NotifyFilters.LastWrite)
watcher.Filter = "*.csv"
watcher.EnableRaisingEvents = True
End Sub
Sub watcher_Created(ByVal source As Object, ByVal e As FileSystemEventArgs) 'This is where the challenge is
' Called when a new file is created in the monitored directory
' Update the corresponding checkbox in the ListView control
Dim fileName As String
fileName = e.Name
' Find the corresponding item in the ListView control
Dim item As listItem
For Each item In UserForm1.ListView1.ListItems
If item.ListSubItems(1).Text = fileName Then
item.Checked = True
Exit For
End If
Next item
End Sub
FileSystemWatcher is not a COM object (it's not listed in HKEY_CLASSES_ROOT\TypeLib), so you cannot create it. It is a .NET class.
I hope that someone else can suggest an alternative.
Apparently the Listview already contains an item with the name of the newly created file.
To check whether a new file has been created in a directory you can use the "scripting.filesystemobject" in the scripting library.
Alternatively you can check every listview.listitem for its existence using Dir(Path & listitem).
The FileSystemWatcher would run silently in the background as various processes were run simultaneously; each process generating a CSV file. The corresponding checkbox in the Listview would be checked once the file showed up in a folder. Think of using the Listview as a report indicating which files completed. The processing time varies so having the FileSystemWatcher run silently makes sense.
I don't believe the "scripting.filesystemobject" approach can run silently in the background. Using this approach I would not be able have the processes run simultaneously.
If I'm missing something, kindly let me know.
I tried capturing the ProcessID of each, wait for the ProcessID to complete and then update the corresponding check box as an alternative method. However having something run in the background such as the FileSystemWatcher made more sense.
1) Use Visual Studio to wrap FileSystemWatcher ina COM interop, and then y9u can use it in VBA just like a normal COM class. Some learning inmvolved, if you don't already know VB.NET or C # or Visual Studio
2) FileSystemWatcher is really just a .net wrapper around the Win32 ReadDirectoryChangesW api call - which can be used by VBA. But there's a fair amount of work to be done to get it running in the background properly.
3) "scripting.filesystemobject" solution - really just polling a folder - can in fact be written to 'run in the background', e.g by using a Timer
