Excel RTD server in Python not updating data

Question

I've got the excelRTDserver.py up and running in Excel 2010 (32bit) by changing the EXCEL_TLB_MINOR value to 7. I can see the server in the add-ins list and if I enter =RTD("Python.RTD.TimeServer","","seconds","5") into a cell, I get the current time. But it never updates. If I change the "5" to another number, I get an update but after the initial change it never changes again.

How do I get it to update? I found someone else with a similar problem here, but no solution.

UPDATE: I've got a little further - there is an exception raised within ServerStart when casting the PyIDispatch callback object into a IRTDUpdateEvent callback object. Using this method to capture the error message, I get "Cannot create a file when that file already exists.". If I follow the suggestion here and use win32com.client.CastTo(CallbackObject,'IRTDUpdateEvent') I get "This COM object can not automate the makepy process - please run makepy manually for this object", but I have already run makepy for Microsoft Excel 12.0 Object Library (1.6).

Any help would be greatly appreciated.

Answer 1

To work around this problem I've created a new project on github for pythoncom excel types:

https://github.com/pyxll/exceltypes

This includes a slightly modified version of excelRTDServer.py that uses the new type PyIRTDUpdateEvent instead of the win32com makepy wrapper, and so it now works in Excel 2010 (look for the comments 'EXCELTYPES_MODIFICATION' in exceltypes/demos/excelRTDServer.py).

To build the project you will need visual studio installed (it won't build with gcc) and you can build it using the setup.py included in the project as follows:

python setup.py install

If you need to force it to use visual studio instead of gcc use the "--compiler=msvc" option, if you're using anaconda for example. If you want to use Visual Studio 2012 instead of the default 2010 add the following lines to setup.py:

from distutils import msvc9compiler
msvc9compiler.VERSION = 11

Answer 2

I think you may be out of luck.

According to the author of excelRTDServer.py in a recent python-win32 thread:

^{The message that this is in response to describes your exact problem, and it's recent, so maybe you already got this info directly, but in case you didn't...}

I fear that things with IRTDUpdateEvent have changed with recent versions of excel (since Excel 2007? I guess that's not so 'recent' anymore...).

While hunting around for news of interface changes, I came across this thread in a java forum:

http://www.nevaobject.com/phpbb3/viewtopic.php?t=516

The part that worries me is this comment:

"Apparently in Excel 12 (Excel 2007) the RTD callback object that implements dual IRTDUpdateEvent interface throws exception (generic COM exception 0x80020009) when is called via IDispatch. If you use v-table binding the call to UpdateNotify succeeds. I don't really know whether it is a bug in Excel 12 or a feature."

So far I haven't been able to confirm this against the MSDN information... But if this is true, it does explain the problem being seen. Many older examples on the web, and pywin32+makepy treat this interface as IDispatch, and wrap it accordingly.

I don't think we can fix this with pywin32 as it is right now. My understanding is that it relies on IDispatch support. May need to look at comtypes (http://starship.python.net/crew/theller/comtypes/) to wrap the (new?) IRTDUpdateEvent objects, or maybe a C extension. :(

Answer 3

Python:

I get "This COM object can not automate the makepy process - please run makepy manually for this object", but I have already run makepy for Microsoft Excel 12.0 Object Library (1.6).

Yesterday at work after a while reading your question, I forgot that is python and not java :)).. Well, the only thing I think now is that seems you need to run the PIA for office 2010.

Edit later: if you steel have problems after what i told you., please comment and not downvote, because this issue is uncommon.

JAVA:

This happen because is missing the option to generate v-tables.

You need to modify ServerStart method and also IRTDServer interface and IRTDServer_Impl class., so CallbackObject is COMIUnknown. Then you need to generate IRTDServer_Skel class by runing the IBuilder.

Now you can generate a new java wrapper for IRTDUpdateEvent to request v-table: