CODEWITHSUNDEEP
vbams-access
mwolfe02
mwolfe02

Reputation: 24227

Receive notification of file creation in VBA without polling

I am writing a program that integrates with a ScanSnap scanner. ScanSnap scanners do not support TWAIN. Once a document is scanned it is automatically saved to a PDF.

I want to monitor the directory where the files will be saved and take some action when the file appears (and is done being written to). A simple approach is to use the MS Access form Timer event and check for an existing file at some small interval of time.

Is there a better alternative via Windows Messaging, the FileSystemObject, or some Windows API function that supports callbacks?

Upvotes: 2

Views: 4333

Answers (4)

Daniel
Daniel

Reputation: 1

Not sure if this really solves your Problem, but here is an approach using Excel VBA that helped me monitor a specific file within a specific Folder and execute certain actions (here: copy the file into another folder) if the file is modified and saved (i.e. when the file's timestamp changes):

Option Explicit

Const SourcePath = "C:\YourFolder\"
Const TargetPath = "C:\YourFolder\YourFolder_Changes\"
Const TargetFile = "YourFileName"

Private m_blnLooping As Boolean

Private Sub CommandButton1_Click()

Dim FSO As Scripting.FileSystemObject
Dim n, msg, dt, inttext As String
Dim file, files As Object
Dim d1, d2 As Date
Dim cnt As Integer
Dim wsshell

Application.ScreenUpdating = False
On Error Resume Next

Set FSO = CreateObject("Scripting.FileSystemObject")
Set files = FSO.GetFolder(SourcePath).files
Set wsshell = CreateObject("WScript.Shell")

msg = "FileWatcher started. Monitoring of " & TargetFile & " in progress."
cnt = 0

'Initialize: Loop through Folder content and get file date
For Each file In files
    n = file.name
    'Get Initial SaveDate of Target File
    If n = TargetFile Then
        d1 = file.DateLastModified
    End If
Next file

m_blnLooping = True

inttext = wsshell.popup(msg, 2, "FileWatcher Ready", vbInformation)
'Message Box should close after 2 seconds automatically

Shell "C:\WINDOWS\explorer.exe """ & TargetPath & "", vbNormalFocus
'Open Windows Explorer and display Target Directory to see changes

Do While m_blnLooping
    For Each file In files
        n = file.name
        If n = TargetFile Then
            d2 = file.DateLastModified
            If d2 > d1 Then
                dt = Format(CStr(Now), "yyyy-mm-dd_hh-mm-ss")
                'FSO.CopyFile (SourcePath & TargetFile), (TargetPath & Left(TargetFile, Len(TargetFile) - 4) & "_" & dt & ".txt"), True  'Option with file name extension
                FSO.CopyFile (SourcePath & TargetFile), (TargetPath & TargetFile & "_" & dt), True                                      'Option without file name extension
                cnt = cnt + 1
                d1 = d2
            End If
        End If
    Next file
    'Application.Wait (Now() + CDate("00:00:02")) 'wait 2 seconds, then loop again
DoEvents
Loop

msg = "File " & TargetFile & " has been updated " & cnt & " times."
inttext = wsshell.popup(msg, 2, "FileWatcher Closed", vbInformation)
'Message Box should close after 2 seconds automatically

Application.ScreenUpdating = True

End Sub

Private Sub CommandButton2_Click()

m_blnLooping = False

End Sub

The procedure is activated via a CommandButton ("START") and loops through the speficied Folder (keeps watching the file) until another CommandButton ("STOP") is pressed. You may, however, need to adjust the code to monitor file creation instead of file changes (file.DateCreated instead of file.DateLastModified). The Code is just meant to provide you hint that might solve your Problem.

Upvotes: 0

mwolfe02
mwolfe02

Reputation: 24227

@Steve effectively answered the question I asked. What I should have asked is how to monitor file system changes in a thread separate from the MS Access UI thread. And the simple answer to that question is that VBA does not support multi-threading in Office applications.

There are a variety of workarounds that generally involve calling an external COM library or integrating with an external application. I decided none of those was very appealing and instead decided to implement the solution in VB.Net using the FileSystemWatcher class.

Upvotes: 1

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

This provides a native WinAP + VB/VBA way of doing the job, I expect:

http://books.google.com/books?id=46toCUvklIQC&pg=PA757&lpg=PA757&dq=windows+api+monitor+directory+changes+vba&source=bl&ots=jmMY4sJFK4&sig=KCB6B_soEA9_JzjlhyNZvSC91w4&hl=en&sa=X&ei=cUAMUsDzOe3iyAHu8YGwAg&ved=0CFIQ6AEwBw#v=onepage&q=windows%20api%20monitor%20directory%20changes%20vba&f=false

Upvotes: 1

stenci
stenci

Reputation: 8481

Nothing inside Excel.

You can create another application that monitors the file system, and executes the Excel macro, opening the workbook if required, opening Excel if required.

Upvotes: 1

Related Questions