Excel 2010 Folder Watcher

Question

0.00/5 (No votes)

See more:

When a file is added to a folder, though API, I want excel to be sent a message which will then initiate other code to run.

I tried the code below which kind of works except one can't do anything else while WaitForSingleObject is waiting. Is there a way to change this so that an event is created only when the file is added to the folder being watched?

VB

Private Const FILE_NOTIFY_CHANGE_ATTRIBUTES = &H4
Private Const FILE_NOTIFY_CHANGE_DIR_NAME = &H2
Private Const FILE_NOTIFY_CHANGE_FILE_NAME = &H1
Private Const FILE_NOTIFY_CHANGE_SIZE = &H8
Private Const FILE_NOTIFY_CHANGE_LAST_WRITE = &H10
Private Const FILE_NOTIFY_CHANGE_SECURITY = &H100
Private Const FILE_NOTIFY_CHANGE_ALL = &H4 Or &H2 Or &H1 Or &H8 Or &H10 Or &H100
Private Declare Function FindFirstChangeNotification Lib "kernel32" Alias "FindFirstChangeNotificationA" (ByVal lpPathName As String, ByVal bWatchSubtree As Long, ByVal dwNotifyFilter As Long) As Long
Private Declare Function FindCloseChangeNotification Lib "kernel32" (ByVal hChangeHandle As Long) As Long
Private Declare Function FindNextChangeNotification Lib "kernel32" (ByVal hChangeHandle As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
Private Declare Function ResetEvent Lib "kernel32" (ByVal hEvent As Long) As Long

Private Sub Form_Load()
    
    Dim Ret As Long
    Ret = FindFirstChangeNotification("C:\", &HFFFFFFFF, FILE_NOTIFY_CHANGE_ALL)
    WaitForSingleObject Ret, &HFFFFFFFF
    MsgBox "Event Triggered for the first time"
    FindNextChangeNotification Ret
    WaitForSingleObject Ret, &HFFFFFFFF
    MsgBox "Event Triggered for the second time"
    
        FindCloseChangeNotification Ret
End Sub

Posted 28-Apr-12 6:17am

Gremblin

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2012-04-28T07:10:00

Solution 1

Instead of API, use Excel OnTime[^] method.

In ThisWorkBook_Open() event

VB

Application.OnTime Now + TimeValue("00:00:15"), "FolderWatcher"

In the new module

VB

Private Sub FolderWatcher()
    'enum files in folder
    'compare with list of loaded file-names
    'do some jobs...

    'call the same procedure in the next 15 sec. -> recursion ;) 
    Application.OnTime Now + TimeValue("00:00:15"), "FolderWatcher"
End Sub

Closing workbook do not forget to stop procedure "FolderWatcher" setting Schedule to False

VB

Application.OnTime Now, "FolderWatcher", ,False

Posted 28-Apr-12 7:10am

Maciej Los

Comments

Sandeep Mewara 28-Apr-12 13:14pm

My 5!

Maciej Los 28-Apr-12 13:48pm

Thank you ;)

Gremblin 28-Apr-12 16:59pm

This works fine except vba has to check at every time interval if a new file has been added, as opposed to only running the script when necessary upon receipt of the API call.

Maciej Los 29-Apr-12 7:57am

I'm not sure why vba (in Excel) is not satisfy you... You can write function to compare the count of files in folder. As you wrote, the function should check when new file is added to the folder... I'll not to persuade you to change your idea.

On this site you'll find information about FindFirstChangeNotification function: "This function does not indicate the change that satisfied the wait condition. To retrieve information about the specific change as part of the notification, use the ReadDirectoryChangesW function."

Excel 2010 Folder Watcher

1 solution

Solution 1

Add your solution here

Preview 0

Excel 2010 Folder Watcher

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us