Excel 2010文件夹观察器 [英] Excel 2010 Folder Watcher

问题描述

当通过API将文件添加到文件夹时，我希望向excel发送一条消息，然后该消息将启动其他代码来运行.

我尝试了下面的代码，在WaitForSingleObject等待时，除了不能执行任何其他操作以外，其他任何方法都无法执行.有没有一种方法可以更改此设置，以便仅在将文件添加到要监视的文件夹时创建事件?

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?

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

推荐答案

使用Excel OnTime (而不是API) [

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

In ThisWorkBook_Open() event

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

在新模块中

In the new module

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

关闭工作簿时不要忘记停止将"FolderWatcher"过程设置为从Schedule设置为False

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

Application.OnTime Now, "FolderWatcher", ,False

这篇关于Excel 2010文件夹观察器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！