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,
- 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.
- Subclassing http.server.HTTPServer and providing overriding implementation of serve_forever that will acknowledge the stop and drop out of the (otherwise infinite) loop.
- 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
