2

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?

mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • You can use vbs with FileSystemObject to keep monitor a particular folder for file changes, compare with filenames in a separate text file/Excel file. Then write the new file details to it. But this will need a shell running in background. – PatricK Aug 15 '13 at 01:29

4 Answers4

1

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.

stenci
  • 8,290
  • 14
  • 64
  • 104
  • That's a thought. I'm actually doing this in MS Access, but the same concepts would apply. The timer event has some annoying [side effects](http://stackoverflow.com/a/10900069/154439), but I think I'd rather deal with those in my particular case. – mwolfe02 Aug 15 '13 at 01:27
  • Usually this kind of jobs can run in background and doesn't require any interface. I usually make a vb application that creates its own hidden instance of Excel, so there are no conflicts with any interactive instance. – stenci Aug 15 '13 at 14:39
  • In my particular case, I do need to integrate the functionality with a user interface. But your approach certainly has merit and may be more useful to future users who stumble upon this question. – mwolfe02 Aug 15 '13 at 15:23
1

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

Steve Rindsberg
  • 14,442
  • 1
  • 29
  • 34
1

@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.

Community
  • 1
  • 1
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
0

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.

Daniel
  • 1
  • 2
  • I appreciate the time you put in to writing up your answer. The technique you are using is polling (i.e., repeatedly checking the file state). I was specifically trying to avoid that approach because it is unnecessarily CPU and I/O intensive. Also, it does not allow other code to run in the meantime. – mwolfe02 Nov 11 '15 at 14:58