[python-win32] Is there anyway to pass a VBA object to a Python COM Server and perform operations?

Diego Vélez Torres

2015-05-12 16:32:53 UTC

Hi:

I'm still working at my COM server.

In my project I'm using VBA functions in Excel which in turn call the
Python COM Server for farther processes. However, the code in Python calls
back into Excel in order to place graphs into the spreadsheet with the
calculated data. The first problem that I have encountered is to know
exactly the spreadsheet where the VBA function is. The typical line of
code:

xlApp = win32com.client.Dispatch('Excel.Application')

doesn't help as it turns back the first Excel instance registered, which
may not be the one where the function is. Even though the Excel may be
correct, the spreadsheet or the workbook may be different.

I found a not so elegant solution by retrieving in VBA the workbook and
sheet names. Later I pass them to the COM server to get the proper object
with a line like this:

sheet_obj = win32com.client.GetObject(fullname)

I tried to simplify the code be passing a VBA object directly to the COM
and treat there in the same way as it is done in VBA. For example, in VBA
I make this:

Set App_Caller = Application.Caller
TheGrap = PythonCom.PlaceGraphic(Param1, Param2, App_Caller)

Meanwhile in the COM code I do something like this:

def PlaceGraphic(self, param1, param2, app_caller):
sheet_obj = app_caller.Worksheet

When I run the code I get the following error message:

'PyIDispatch' object has no attribute 'Worksheet'

Is there anyway to pass a VBA object to a Python COM Server and perform
operations?

Cheers to all,