VBA, Python - Python Web Server housed as a COM component

In this post I give code for a Python web server housed as a COM component which is startable and stoppable from VBA or any other COM-enabled client. The code demonstrates COM server code, Python web server code, multi-threading and Python logging.

Multithreading possible but ill-advised in VBA

Multithreading in VBA is technically possible as VBA code can access Windows API functions such as CreateThread as well as the operating system artefacts used to manage concurrency and synchronization such as semaphores, critical sections and mutexs. Unfortunately, if you create threads in VBAs and then place breakpoints in the code to debug then Excel will crash because the Excel VBA IDE is not multi-threading aware/capable. Never mind, for multi-threading problems an Excel VBA developer can co-opt either C# (or other .NET languages) or Python to build a COM component callable from VBA. In this post I use Python.

Code commentary - the COM server code

The code below demonstrates COM server code which keen readers of this blog will have seen many times before so I will be brief. The StarterAndStopper class (excerpt given below) is the COM server gateway class, we can tell this from the _reg_clsid and _reg_progid attributes as well as the list of methods. Also there is a key line of code which determines how to implement the COM server's housing; _reg_clsctx_ which if omitted defaults to an in-process DLL pattern but if pythoncom.CLSCTX_ instead then the COM server will be housed in a separate .Exe. This is extremely useful during development for tearing down one instance and replace with another implementing the latest changes.

class StarterAndStopper(object):
    ...
    _reg_clsid_ = "{2D23D974-73B1-4106-9096-DA6006BD84AA}"
    _reg_progid_ = 'PythonInVBA.StarterAndStopper'
    _public_methods_ = ['StartWebServer','StopWebServer','CheckThreadStatus','StopLogging']
    ##_reg_clsctx_ = pythoncom.CLSCTX_ ## uncomment this for a separate COM Exe server instead of in-process DLL server

the registration code is given in the following lines, these need to be run once; if not with Admin rights then an escalation is requested.

def RegisterCOMServers():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(StarterAndStopper)

if __name__ == '__main__':
    #run()
    RegisterCOMServers()

then once registered the COM server is creatable with the following CreateObject line of code...

    Set mobjPythonWebServer = VBA.CreateObject("PythonInVBA.StarterAndStopper")

I will give further commentary of this class later when talking about multi-threading.

Code commentary - stoppable web server code

So we utilize the Python library's basic web server, this is not for use unless behind a firewall but is usable for facilitating HTTP communication between programs on the same computer. For robust internet-facing industrial strength production web serving one should use Apache web server with a Python plug-in. For my purposes the basic web server is fine, I am planning some code where the browser on a machine calls into Excel.exe running on the same machine, i.e. we are not internet-facing.

The base class http.server.HTTPServer has a serve_forever method which runs in an infinite loop which only interrupts when Ctrl+C is pressed on the keyboard in the console window in which the web server is running. If running in a COM server housing then there is no visible console and so we need a mechanism to stop the web server without a keyboard interrupt. The code in an article over on activestate.com gives the pattern for a stoppable web server by amending the standard implementation thus,

1. Adding an additional HTTP verb handler to the class derived from SimpleHTTPRequestHandler to handle a QUIT request. The code here sets a Stop flag to True.
2. Subclassing http.server.HTTPServer and providing overriding implementation of serve_forever that will acknowledge the stop and drop out of the (otherwise infinite) loop.
3. In the shutdown code make a HTTP QUIT request to one's own webserver

class MyRequestHandler(SimpleHTTPRequestHandler):
    ...
    def do_QUIT (self):
            # http://code.activestate.com/recipes/336012-stoppable-http-server/ 
            """send 200 OK response, and set server.stop to True"""
            self.send_response(200)
            self.end_headers()
            self.server.stop = True
            self.wfile.write("quit called".encode('utf-8'))

class StoppableHttpServer(HTTPServer):
    # http://code.activestate.com/recipes/336012-stoppable-http-server/ 
    """http server that reacts to self.stop flag"""

    def serve_forever (self):
            """Handle one request at a time until stopped."""
            self.stop = False
            while not self.stop:
                self.handle_request()

class StarterAndStopper(object):
    def StopWebServer(self):

                    ## make a quit request to our own server 
                    quitRequest  = urllib.request.Request("http://" + self.server_name + ":" + str(self.server_port) + "/quit",
                                                      method="QUIT")
                    with urllib.request.urlopen(quitRequest ) as resp:
                        logging.info("StarterAndStopper.StopWebServer      : quit response '" + resp.read().decode("utf-8") + "'")

Whilst on the subject of no visible console window, we have to redirect stdout and stderr to somewhere, e.g. a file otherwise the code complains and throws errors. So I found adding the following is sufficient to suppress such errors.

        sys.stderr = open((os.path.dirname(os.path.realpath(__file__))) + '\\logfile.txt', 'w', buffer)
        sys.stdout = open((os.path.dirname(os.path.realpath(__file__))) + '\\logfile.txt', 'w', buffer)

Code commentary - multithreading

Creating and starting a new thread in Python is quite simple using the Thread constructor threading.Thread(name, target, args) where target is a function or a class's method, in this case a standalone function called thread_function which itself simply calls the web server's serve_forever method given above. Once constructed, we call the Thread's start method.

class StarterAndStopper(object):
    def StartWebServer(self,foo, bar: str, baz: str, server_name:str, server_port: int):
            self.running = False 
            
            self.httpd = StoppableHttpServer((server_name, server_port), MyRequestHandler)

            self.serverthread = threading.Thread(name="webserver", target=thread_function, args=(self,))
            self.serverthread.setDaemon(True)
            
            self.serverthread.start()
            ... 

def thread_function(webserver):
    try:
        webserver.httpd.serve_forever()  #code enters into the subclass's implementation, an almost infinite loop
        ...

When we come to stop the web server by sending the QUIT HTTP request notifying the web server thread of close down we then call the Thread.join method on the main thread to wait for the web server thread to drop off. In the code given we set the Thread to a daemon, which means the Thread's refusal to finish does not prevent unloading the code once the main thread has finished.

Code commentary - developing a multithreaded COM component

The code is meant to be executed as a COM component with execution beginning with a COM client such as VBA. Unfortunately such a scenario does not facilitate hitting break points and stepping through the source code. For this reason a separate run() function is found at the bottom of the code. This is to be run in Microsoft Visual Studio and doing this we get to hit break points and step through the code. Sometimes, it's necessary to comment out the setDaemon(True) line so that the code does not unload, allowing continued debugging. This can be a bit of pain but until I can get the breakpoints to hit in the original scenario I will have to persist with this.

Code commentary - Python logging

In addition to the lack of breakpoints in the primary one use case (see above) the code can be difficult to debug because of the nature of multithreading. One cannot always tell the order in what events occurred! To solve this I put in the code a ton of logging so that I could see just what precisely is happening. Here is a sample of my log which expresses the sequence of events for starting the web server, using a browser to make a HTTP GET, then stopping the web server. In fact this log says so much more than any prose that I could write.

22:37:17: StarterAndStopper.StartWebServer     : server_name: localhost, server_port:8014
22:37:17: StarterAndStopper.StartWebServer     : about to create thread
22:37:17: StarterAndStopper.StartWebServer     : about to start thread
22:37:17: StarterAndStopper.StartWebServer     : after call to start thread
22:37:17: thread_function                      : about to enter webserver.httpd.serve_forever
22:37:17: StoppableHttpServer.serve_forever    : entered
22:37:20: MyRequestHandler.do_GET              : entered.  path=/testurl
22:37:20: StoppableHttpServer.serve_forever    : request successfully handled self.stop=False
22:37:22: StarterAndStopper.StopWebServer      : entered
22:37:22: StarterAndStopper.StopWebServer      : call quit on own web server
22:37:24: MyRequestHandler.do_QUIT             : entered
22:37:24: MyRequestHandler.do_QUIT             : setting self.server.stop = True
22:37:24: StoppableHttpServer.serve_forever    : request successfully handled self.stop=True
22:37:24: StarterAndStopper.StopWebServer      : quit response 'quit called'
22:37:24: StoppableHttpServer.serve_forever    : dropped out of the loop
22:37:24: StarterAndStopper.StopWebServer      : about to join thread
22:37:24: thread_function                      : returned from webserver.httpd.serve_forever
22:37:24: thread_function                      : finished
22:37:24: StarterAndStopper.StopWebServer      : thread joined
22:37:24: StarterAndStopper.StopWebServer      : about to call httpd.server_close()
22:37:24: StarterAndStopper.StopWebServer      : completed

Full Code Listings

So here is the full Python code listing which has all the full logging statements in it.

import sys
import time #sleep
import http.server
import threading
import tempfile
import os

import win32com.client
from io import BytesIO
import pythoncom

import urllib.request

from http.server import HTTPServer, BaseHTTPRequestHandler, SimpleHTTPRequestHandler
import logging

class MyRequestHandler(SimpleHTTPRequestHandler):

    def do_GET(self):
        try:
            logging.info("MyRequestHandler.do_GET              : entered.  path=" + self.path)
            self.send_response(200)
            self.send_header('Content-type', 'text/html')
            self.end_headers()
            if (self.path != r"/favicon.ico"):
                self.wfile.write("GET request for {}".format(self.path).encode('utf-8'))
                self.wfile.write((" default response").encode('utf-8'))
        except Exception as ex:
            logging.info("MyRequestHandler.do_GET   error   : " + 
                LocalsEnhancedErrorMessager.Enhance(ex,str(locals())))

    def do_POST(self):
        try:
            logging.info("MyRequestHandler.do_POST             : entered ")
            content_length = int(self.headers['Content-Length']) # <--- Gets the size of data
            post_data = self.rfile.read(content_length) # <--- Gets the data itself

            self.send_response(200)
            self.send_header('Content-type', 'text/html')
            self.end_headers()

            msgBytesReceived = "POST body:" + str(len(post_data)) + " bytes received" 

            response = BytesIO()
            response.write(msgBytesReceived.encode('utf-8'))

            self.wfile.write(response.getvalue())
            self.wfile.flush()

            print(msgBytesReceived)

            logging.info("MyRequestHandler.do_POST             : " + msgBytesReceived)

        except Exception as ex:
            logging.info("MyRequestHandler.do_POST  error    : " + 
                LocalsEnhancedErrorMessager.Enhance(ex,str(locals())))

    def do_QUIT (self):
        try:
            logging.info("MyRequestHandler.do_QUIT             : entered")
            """send 200 OK response, and set server.stop to True"""
            self.send_response(200)
            self.end_headers()
            logging.info("MyRequestHandler.do_QUIT             : setting self.server.stop = True")
            self.server.stop = True
            self.wfile.write("quit called".encode('utf-8'))
        except Exception as ex:
            logging.info("MyRequestHandler.do_QUIT  error    : " + 
                LocalsEnhancedErrorMessager.Enhance(ex,str(locals())))

class LocalsEnhancedErrorMessager(object):
    @staticmethod
    def Enhance(ex, localsString):
        locals2 = "n Locals:{ " + (",n".join(localsString[1:-1].split(","))) + " }"
        if hasattr(ex,"message"):
            return "Error:" + ex.message + locals2
        else:
            return "Error:" + str(ex) + locals2

def thread_function(webserver):
    try:
        pythoncom.CoInitialize() # need this to tell the COM runtime that a new thread exists
        webserver.running = True 

        ## we need to pipe output to a file because whilst running as COM server there is no longer a console window to print to
        buffer = 1
        sys.stderr = open((os.path.dirname(os.path.realpath(__file__))) + '\logfile.txt', 'w', buffer)
        sys.stdout = open((os.path.dirname(os.path.realpath(__file__))) + '\logfile.txt', 'w', buffer)

        logging.info("thread_function                      : about to enter webserver.httpd.serve_forever")
        webserver.httpd.serve_forever()  #code enters into the subclass's implementation, an almost infinite loop
        logging.info("thread_function                      : returned from webserver.httpd.serve_forever")
        
        logging.info("thread_function                      : finished")

    except Exception as ex:
        logging.info("thread_function   error   : " + 
            LocalsEnhancedErrorMessager.Enhance(ex,str(locals())))

class StoppableHttpServer(HTTPServer):
    # http://code.activestate.com/recipes/336012-stoppable-http-server/ 
    """http server that reacts to self.stop flag"""

    def serve_forever (self):
        try:
            logging.info("StoppableHttpServer.serve_forever    : entered")
            """Handle one request at a time until stopped."""
            self.stop = False
            while not self.stop:
                self.handle_request()
                logging.info("StoppableHttpServer.serve_forever    : request successfully handled self.stop=" + str(self.stop))
            logging.info("StoppableHttpServer.serve_forever    : dropped out of the loop")
        except Exception as ex:
            logging.info("StoppableHttpServer.serve_forever  error   : " + 
                LocalsEnhancedErrorMessager.Enhance(ex,str(locals())))
            
class StarterAndStopper(object):
    import logging
    import threading
    import time
    
    _reg_clsid_ = "{2D23D974-73B1-4106-9096-DA6006BD84AA}"
    _reg_progid_ = 'PythonInVBA.StarterAndStopper'
    _public_methods_ = ['StartWebServer','StopWebServer','CheckThreadStatus','StopLogging']
    ##_reg_clsctx_ = pythoncom.CLSCTX_ ## uncomment this for a separate COM Exe server instead of in-process DLL server

    def StopLogging(self):
        try:
            logging.shutdown()
            return "logging.shutdown() ran"
        except Exception as ex:
            msg = "StarterAndStopper.StopLogging error:" + LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
            logging.info(msg)
            return msg

    def StartWebServer(self,foo, bar: str, baz: str, server_name:str, server_port: int):
        try:
            self.server_name = server_name
            self.server_port = server_port

            logging.basicConfig(filename =  (os.path.dirname(os.path.realpath(__file__))) + '\app2.log', format="%(asctime)s: %(message)s", 
                        level=logging.INFO, datefmt="%H:%M:%S")

            logging.info("StarterAndStopper.StartWebServer     : server_name: " + server_name + ", server_port:" + str(server_port))

            self.running = False 
            
            self.httpd = StoppableHttpServer((server_name, server_port), MyRequestHandler)

            logging.info("StarterAndStopper.StartWebServer     : about to create thread")

            self.serverthread = threading.Thread(name="webserver", target=thread_function, args=(self,))
            self.serverthread.setDaemon(True)
            logging.info("StarterAndStopper.StartWebServer     : about to start thread")

            self.serverthread.start()
            logging.info("StarterAndStopper.StartWebServer     : after call to start thread")
            
            return "StartWebServer ran ok ( server_name: " + server_name + ", server_port:" + str(server_port) + ")"

        except Exception as ex:
            msg = "StarterAndStopper.StartWebServer error:" +  LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
            logging.info(msg)
            return msg

    def CheckThreadStatus(self):
        try:
            # Clear the stream now that we have finished
            global callbackInfo

            if self.running:
                if hasattr(self,'httpd') :
                    logging.info("StarterAndStopper.CheckThreadStatus    : checking thread status")
                    return self.serverthread.is_alive()
                else:
                    return "StopWebServer ran ok, nothing to stop"
            else:
                return "StopWebServer ran ok, nothing to stop"

        except Exception as ex:
            msg = "StarterAndStopper.CheckThreadStatus error:" +  LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
            logging.info(msg)
            return msg

    def StopWebServer(self):
        try:
            retMsg = "StopWebServer ran (default)"
            logging.info("StarterAndStopper.StopWebServer      : entered")

            if self.running:
                if hasattr(self,'httpd') :

                    logging.info("StarterAndStopper.StopWebServer      : call quit on own web server")
                    ## make a quit request to our own server 
                    quitRequest  = urllib.request.Request("http://" + self.server_name + ":" + str(self.server_port) + "/quit",
                                                      method="QUIT")
                    with urllib.request.urlopen(quitRequest ) as resp:
                        logging.info("StarterAndStopper.StopWebServer      : quit response '" + resp.read().decode("utf-8") + "'")

                    # web server should have exited loop and its thread should be ready to terminate
                    logging.info("StarterAndStopper.StopWebServer      : about to join thread")
                    self.serverthread.join()    # get the server thread to die and join this thread
                    self.running = False 
                    
                    logging.info("StarterAndStopper.StopWebServer      : thread joined")

                    logging.info("StarterAndStopper.StopWebServer      : about to call httpd.server_close()")
                    self.httpd.server_close()  #now we can close the server cleanly
                    
                    logging.info("StarterAndStopper.StopWebServer      : completed")

                    retMsg = "StopWebServer ran ok, web server stopped"
                else:
                    retMsg = "StopWebServer ran ok, nothing to stop"
            else:
                retMsg = "StopWebServer ran ok, nothing to stop"
            return retMsg

        except Exception as ex:
            msg = "StarterAndStopper.StopWebServer error:" +  LocalsEnhancedErrorMessager.Enhance(ex,str(locals()))
            print(msg)
            logging.info(msg)
            return msg

def run():
    # this code is to be run in Microsoft Visual Studio by pressing F5
    # use this code to step through and debug the web server portion of code 
    try:

        print("Executing run")
        print((os.path.dirname(os.path.realpath(__file__))))

        logging.basicConfig(filename = (os.path.dirname(os.path.realpath(__file__))) + '\app2.log', format="%(asctime)s: %(message)s", 
                        level=logging.INFO, datefmt="%H:%M:%S")

        ws = StarterAndStopper()
        ws.StartWebServer(None,None, None,'localhost',8009)

        logging.info('called StarterAndStopper.StartWebServer ...n')

        if False:

            logging.info('what next? ...n')
            ws.StopWebServer()

            logging.info('finishing run()n')
    except Exception as ex:
        print(ex)

def RegisterCOMServers():
    print("Registering COM servers...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(StarterAndStopper)

if __name__ == '__main__':
    run()
    #RegisterCOMServers()

And here is the client VBA code which calls into the COM server (ensure it is registered!).

Option Explicit
Option Private Module

Dim mobjPythonWebServer As Object

Public Const PORT As Long = 8014

Function TestPythonVBAWebserver_StartWebServer()
    Set mobjPythonWebServer = VBA.CreateObject("PythonInVBA.StarterAndStopper")

    Debug.Print mobjPythonWebServer.StartWebServer(Null, Null, Null, "localhost", PORT)

End Function

Sub TestPythonVBAWebserver_StopWebServer()
    If Not mobjPythonWebServer Is Nothing Then
        Debug.Print mobjPythonWebServer.StopWebServer
    End If
End Sub

Sub TestPythonVBAWebserver_StopLogging()
    '# This releases the log file so I can delete it occassionally
    If Not mobjPythonWebServer Is Nothing Then
        Debug.Print mobjPythonWebServer.StopLogging
    End If
End Sub

Sub PickupNewPythonScript()
    '# for development only to help pick up script changes we kill the python process
    Call CreateObject("WScript.Shell").Run("taskkill /f /im pythonw.exe", 0, True)
    Set mobjPythonWebServer = Nothing
End Sub

VBA, Python, Multi-threading - Stoppable Folder Replicator

In this post I give a multi-threaded Python script which replicates one folder to another. It uses the Windows API ReadDirectoryChangesW function to watch for directory changes. It is start-able and stoppable from Excel VBA using the Windows Synchronization Events which I explored in the previous couple of posts.

Background

So I wanted some code to replicate a folder. Of course I could have written some VBA code to scan both a source and a destination folder every few seconds and figure what needed copying but this is a bit lame because it has needless disk activity and other techniques must be available. It is possible to use the Windows API, and specifically the ReadDirectoryChangesW function to watch a folder. .NET also has a folder watcher feature but I am happy to stick to Windows API.

ReadDirectoryChangesW blocks (in synchronous case)

But the problem with ReadDirectoryChangesW is that is blocks in the synchronous case. It is not like other waitable API function such as WaitForSingleObject(handle,interval) in that one can wait for a fixed time interval before breaking off to do some other work and give the impression of multi-threading.

I have seen on some VB6 forums some programmers attempt to use CreateThread() to give themselves another thread but they report crashes and this is because threads in VB6 and VBA need to obey COM apartment threading rules. I have yet to see successfully robust multi-threaded code for VB6 or VBA. Sure, I've seen code that looks like it runs multiple threads but the moment one puts in a break point and starts debugging Excel crashes. Perhaps one day I might solve that problem. In the meantime, I strongly recommend *not* creating threads in VBA. If you want concurrency then you should shell another process.

However, shelling another process raises the issue on how to communicate between the processes. In the previous two posts I demonstrated the use of Windows Synchronization Events as an inter process communication (IPC) mechanism and I will use this to signal the folder replicator to stop. To shell a child process on the VBA side, I am using Windows Script Host Object Model (wshom.ocx) and at some point the code could call Terminate() to kill the shelled process but this does not give the shelled process a chance to tidy up its resources. Instead, I am going to give VBA code which signals an Windows Synchronization Event object to politely ask the shelled process to tidy up and terminate itself.

ReadDirectoryChangesW is waitable in the asynchronous case

So upon further researching it turns out ReadDirectoryChangesW has a asynchronous use case as well which is helpful. Even more helpful is the fact I discovered Python code to call ReadDirectoryChangesW asynchronously in the test scripts of Mark Hammond's Win32 library, test_win32file.py. I am truly grateful for Mark Hammond's Windows Python library. For an explanation as to how the asynchronous use case works see the remarks section of ReadDirectoryChangesW docs page. So in the Python code it shows we can call WaitForSingleObject(handle,interval) in the asynchronous case.

But we still need two threads, one will watch the directory for changes and the other will wait for the stop signal to be issued by the Excel VBA code. So the following Python script is an example of how to launch a second thread in Python.

StoppableFolderReplicator.py

The core of the Python script below is based upon a unit test file for Mark Hammond's Windows Python library, test_win32file.py. The main() function runs first and validates the command line arguments, if valid it creates the class StoppableFolderReplicator where the real action takes place.

In the constructor of StoppableFolderReplicator class a handle to the directory to watch is obtained and then a second Thread is created to run the class's _watcherThreadOverlapped() method. Then the main thread runs the class's waitForEvent() method where it sits waiting for the Windows Synchronization Event (the stop event) to be signalled (by VBA).

The second thread runs the class's _watcherThreadOverlapped() method and loops around watching the directory by calling the ReadDirectoryChangesW() function. ReadDirectoryChangesW() returns either on a timeout or if there is a change in the directory it is watching; if the latter then any new file is copied across. Each iteration of the loop this thread also checks to the class's tearDownInProgress boolean property which is signalled by the main thread (in waitForEvent()) when the stop event is signalled; if signalled this second thread releases its handle and then quits.

A key line of code in the class's waitForEvent() method is self.watcher_thread.join(5) where the first thread pauses and waits for the second thread to acknowledge the stop, tidy up and then quit. Without this line, the main thread would quit earlier than the second thread with unexpected results.

Most messages printed are prefixed by a thread number to help folks understand how it works.

import win32api
import win32file
import pywintypes
import winerror
import win32event
import win32con
import ntsecuritycon
import sys
import os
import tempfile
import threading
import time
import shutil
from shutil import copyfile

class StoppableFolderReplicator(): 

    def __init__(self, srcFolder, destFolder, hEvent):
        self.srcFolder = srcFolder
        self.destFolder = destFolder
        self.hEvent = hEvent
        self.tearDownInProgress = False

        self.ACTIONS = {
                1 : "Created",
                2 : "Deleted",
                3 : "Updated",
                4 : "Renamed from something",
                5 : "Renamed to something"
            }
        
        self.dir_handle = win32file.CreateFile(srcFolder, 
                                        ntsecuritycon.FILE_LIST_DIRECTORY,
                                        win32con.FILE_SHARE_READ,
                                        None, # security desc
                                        win32con.OPEN_EXISTING,
                                        win32con.FILE_FLAG_BACKUP_SEMANTICS | win32con.FILE_FLAG_OVERLAPPED,
                                        None)

        self.watcher_thread = threading.Thread(target=self._watcherThreadOverlapped,
                            args=(srcFolder, destFolder, self.dir_handle))
        self.watcher_thread.start()


    def _watcherThreadOverlapped(self, srcFolder, destFolder, dir_handle):
        print("thread #2:waiting for directory changes")
        changes = []
        flags = win32con.FILE_NOTIFY_CHANGE_FILE_NAME
        buf = win32file.AllocateReadBuffer(8192)
        overlapped = pywintypes.OVERLAPPED()
        overlapped.hEvent = win32event.CreateEvent(None, 0, 0, None)
        while 1:
            if self.tearDownInProgress:
                print("thread #2:tidying up")
                if self.dir_handle != 0:
                    win32api.CloseHandle(self.dir_handle)
                    self.dir_handle = 0
                break
            try:
                win32file.ReadDirectoryChangesW(self.dir_handle,
                                                buf,
                                                False, #sub-tree
                                                flags,
                                                overlapped)
            except Exception as e:
                print("thread #2: Exception whilst ReadDirectoryChangesW:" + str(e) + "\n")
                break 
            # Wait for our event or for a short time.
            rc = win32event.WaitForSingleObject(overlapped.hEvent, 1000)
            if rc == win32event.WAIT_OBJECT_0:
                # got some data!  Must use GetOverlappedResult to find out
                # how much is valid!  0 generally means the handle has
                # been closed.  Blocking is OK here, as the event has
                # already been set.
                nbytes = win32file.GetOverlappedResult(dir_handle, overlapped, True)
                if nbytes:
                    bits = win32file.FILE_NOTIFY_INFORMATION(buf, nbytes)
                    changes.extend(bits)

                    for action, file in changes:
                        full_filename = os.path.join(srcFolder, file)
                        print("thread #2:" , full_filename, self.ACTIONS.get(action, "Unknown"))
                        if action == 1 or action == 3:
                            #
                            # perhaps put some filtering or file renaming logic
                            # in here
                            #
                            if os.path.isfile(full_filename):
                                sDestFilename = os.path.join(destFolder, file)
                                try:
                                    copyfile(full_filename,sDestFilename)
                                except Exception as e:
                                    print("thread #2: Exception whilst copying file '" + full_filename + "':" + str(e) + "\n")
                    changes = []  # bugfix, need to clear this other it accumulates
                else:
                    # This is "normal" exit - our 'tearDown' closes the
                    # handle.
                    # print "looks like dir handle was closed!"
                    break
        print ("thread #2:stopped")

    def waitForEvent(self):
        try:
            print("thread #1:waiting for stop event")
            while 1:
                
                dwWaitResult = win32event.WaitForSingleObject(self.hEvent.handle, 500)
                if dwWaitResult == win32event.WAIT_OBJECT_0:
                    print("thread #1:stop signal received...")
                    self.tearDownInProgress = True
                    self.watcher_thread.join(5)
                    return
                pass

        except Exception as e:
            print("thread #1:Exception whilst polling for event:'" + self.stopEventName + "':" + str(e) + "\n")

        
if __name__ == '__main__':

    if len(sys.argv) < 4:
        print("Usage:" + os.path.basename(__file__) + " <sourceFolder> <destinationFolder> <stopEventName>")
    else:
        srcFolder = sys.argv[1]
        destFolder = sys.argv[2]
        stopEventName = sys.argv[3]

        ok = True
        if not os.path.isdir(srcFolder):
            print("Error, srcFolder '" + srcFolder + "' does not exist!  Abandoning.")
            ok = False

        if not os.path.isdir(destFolder):
            print("Error, destFolder '" + destFolder + "' does not exist!  Abandoning.")
            ok = False

        if ok == True:
            try:
                stoppable = False
                hEvent = win32event.OpenEvent(ntsecuritycon.SYNCHRONIZE, 0, stopEventName)
                
                stoppable = True
            except Exception as e:
                print("Exception whilst opening event:'" + stopEventName + "':" + str(e) + "\n")

            if stoppable:
                foo = StoppableFolderReplicator(srcFolder, destFolder, hEvent) 
                foo.waitForEvent()
                win32api.CloseHandle(hEvent)
                print("all stopped")
            else:
                print("Not stoppable, abandoning!")

Calling Excel VBA code

So two lines of the VBA code depend upon Chip Pearson's FormatMessage() function is a freely downloadable module from his website; alternatively you could comment them out.

In the previous two posts, I wrote about Windows Synchronization Event objects and how to use them in VBA so I won't duplicate explanation of that.

You should run the TestStoppableFolderReplicator() procedure. The other procedure is there for debugging against a Visual Studio launched Python process and is useful for development.

The stop event is created before the Python script is shelled because the Python script is expecting to open the event object; we create the event object in an initial state of being unset.

Next, the code uses the Windows Script Host Object Model (wshom.ocx) type library to shell the Python script, we supply three extra arguments after the script name (i) source folder, (ii) destination folder and (iii) the stop event name.

The code then reaches a Stop statement at which point you are invited to pop off to the source folder and create some files to test the replication. For the quickest way to create a file, I right click in a folder's explorer window and in the context menu I select the menu option New->Text Document. When a file is created it is replicated to the destination folder specified.

Once you've got bored of creating files and seeing them replicated then press F5 to move off the Stop statement and send the stop signal, the code then loops waiting for the Python process to quit voluntarily, this should be for only a split second.

When running the code below the shelled process appears as a blank window but once finished the output to that window is displayed in the VBA IDE's Immediate window. Underneath the code listing is a sample of what appeared for me in the Immediate window.

Option Explicit

'* Tools->References
'*   Windows Script Host Object Model (wshom.ocx)

Private Declare Function CreateEventWithoutSec Lib "kernel32" Alias "CreateEventA" (lpEventAttributes As Long, _
                    ByVal bManualReset As Long, ByVal bInitialState As Long, ByVal lpName As String) As Long
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function SetEvent Lib "kernel32.dll" (ByVal hEvent As Long) As Long
Private Declare Function ResetEvent Lib "kernel32.dll" (ByVal hEvent As Long) As Long

Private hEvent As Long



Sub TestStoppableFolderReplicator()

    Dim oWshShell As IWshRuntimeLibrary.WshShell
    Set oWshShell = New IWshRuntimeLibrary.WshShell

    Const csPythonScriptFilePath As String = "C:\Users\Simon\source\repos\StoppableFolderReplicator\StoppableFolderReplicator\StoppableFolderReplicator.py "
    Const csSourceFolder As String = """N:\Folder Replication Sandbox\Source"""
    Const csDestinationFolder As String = """N:\Folder Replication Sandbox\Destination"""
    Const csStopEvent As String = "stopReplicator"
    
    Dim sCmdLine As String
    sCmdLine = "python " & csPythonScriptFilePath & " " & csSourceFolder & " " & csDestinationFolder & " " & csStopEvent
    
    hEvent = CreateEventWithoutSec(0, 1, 0, csStopEvent)
    If hEvent = 0 Then
        Debug.Print GetSystemErrorMessageText(Err.LastDllError)
        GoTo SingleExit
    End If
    
    '*
    '* launch the python script
    '*
    Dim oWshExec As IWshRuntimeLibrary.WshExec
    Set oWshExec = oWshShell.Exec(sCmdLine)
    Debug.Assert oWshExec.Status = WshRunning

    '* stop here for a while, pop off to source folder and create some files
    '* and watch them replicate to destination folder
    '* once you've got bored press F5 to send the stop signal
    Stop
    
    '* Ill-advised to use oWshExec.Terminate() because of resources not being tidied up cleanly
    '* so instead we set a signal which the shelled process looks for a shutdowns cleanly and voluntarily
    Call SetEvent(hEvent)

    '* allow the Python script a split second to respond the stop signal
    '* in the meantime we'll loop until the Python process quits
    While oWshExec.Status = WshRunning
        DoEvents '* stay responsive
    Wend

    '* very important to reset event if you want subsequent runs to work as well!
    Call ResetEvent(hEvent)
    Debug.Assert oWshExec.Status = WshFinished
    
    '* we can access what would have been printed to the console
    Debug.Print oWshExec.StdOut.ReadAll
    
    '* tidy up our handle, very important!
SingleExit:
    If hEvent <> 0 Then
        Call CloseHandle(hEvent)
        hEvent = 0
    End If

End Sub

Private Sub DebugStopSignal()
    '* I used this procedure for debugging the stop signal
    hEvent = CreateEventWithoutSec(0, 1, 0, "stopReplicator")
    If hEvent = 0 Then
        Debug.Print GetSystemErrorMessageText(Err.LastDllError)
        GoTo SingleExit
    Else
        Stop
        Call SetEvent(hEvent)
    
        Stop
        Call ResetEvent(hEvent)
    End If
    
SingleExit:
    If hEvent <> 0 Then
        Call CloseHandle(hEvent)
        hEvent = 0
    End If
End Sub

Here is some sample output copied from my Immediate window

thread #2:waiting for directory changes
thread #1:waiting for stop event
thread #2: N:\Folder Replication Sandbox\Source\New Text Document.txt Created
thread #2: N:\Folder Replication Sandbox\Source\New Text Document.txt Created
thread #2: N:\Folder Replication Sandbox\Source\New Rich Text Document.rtf Created
thread #1:stop signal received...
thread #2:tidying up
thread #2:stopped
all stopped

Final thoughts

The Python script replicated a folder but it could do anything that you wanted to offload from VBA because of VBA's lack of multi-threading. Signalling a stop is a vital IPC mechanism. This is much better than just calling the Terminate() method. I really like Python as a means of extending VBA's power. Enjoy!

Links

• Microsoft Docs - ReadDirectoryChangesW function