** For an up to date copy of this code go to the pyxll-examples github repo **https://github.com/pyxll/pyxll-examples** For Excel 2010 and later version you will need the exceltypes package **https://github.com/pyxll/exceltypes
I've been playing around a little bit with real time data (RTD) functions, and thought I'd post something here in case anyone else was interested.
Using win32com and Chris Nilsson's example excelRTDServer.py (one of the win32com demos) it's pretty easy to get an RTD server working which you can then call from Excel with a formula like:=RTD("Python.RTD.TimeServer","","seconds","5")
The only slight problems I have with this are:
- the RTD function is not a very user friendly way to present a function to a user
- registering the server requires access to the HKEY_CLASSES_ROOT, and most people shouldn't have that access
In VBA, it's possible to write a wrapper function around the RTD call to make it look like a normal worksheet function. Exactly the same thing is possible using PyXLL:
from pyxll import xl_func, GetActiveObject
@xl_func("int seconds: var")
xl = win32com.client.Dispatch(GetActiveObject()).Application
return xl.WorksheetFunction.RTD("Python.RTD.TimeServer", "", "seconds", str(seconds))
Now the user can enter a simpler formula and still get real time data back:=py_rtd_test(5)
It's possible to register COM controls under HKEY_CURRENT_USER instead of HKEY_CLASSES_ROOT, it's just that win32com doesn't currently give you that choice. Luckily registering a COM control isn't actually that hard, you just have to know what values to put in the registry. Here's the function I used to register the server:
"""register an inproc com server in hkey_current_user"""
clsid_path = "Software\\Classes\\CLSID\\" + cls._reg_clsid_
progid_path = "Software\\Classes\\" + cls._reg_progid_
spec = cls.__module__ + "." + cls.__name__
# register the class information
win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path, win32con.REG_SZ, cls._reg_desc_)
win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path + "\\ProgID", win32con.REG_SZ, cls._reg_progid_)
win32api.RegSetValue(win32con.HKEY_CURRENT_USER, clsid_path + "\\PythonCOM", win32con.REG_SZ, spec)
hkey = win32api.RegCreateKey(win32con.HKEY_CURRENT_USER, clsid_path + "\\InprocServer32")
win32api.RegSetValueEx(hkey, None, None, win32con.REG_SZ, pythoncom.__file__)
win32api.RegSetValueEx(hkey, "ThreadingModel", None, win32con.REG_SZ, "Both")
# and add the progid
win32api.RegSetValue(win32con.HKEY_CURRENT_USER, progid_path, win32con.REG_SZ, cls._reg_desc_)
win32api.RegSetValue(win32con.HKEY_CURRENT_USER, progid_path + "\\CLSID", win32con.REG_SZ, cls._reg_clsid_)
I added a call to _register(win32com.demos.excelRTDServer.TimeServer) to my module imported by PyXLL so the server would get registered whenever the addin was loaded.
I've attached a really simple example that registers the win32com demo server and exposes it via a worksheet function. If you try this you should note that the excelRTDServer.py has the typelib version hardcoded to 1.4. To make it work with Excel 2003 I had to change EXCEL_TLB_MINOR to 5.