Unable to open Excel workbook

[infinity77]

Hi,

I am using comtypes 1.1.3 on Windows 7 64bit, Python 2.7.3 64bit. Using this code:

import comtypes.client

XLS = r"D:\Working\ExcelFile.xlsm"

xls_app = comtypes.client.CreateObject("Excel.Application")
xls_app.Workbooks.Open(XLS, False, False)
xls_app.Quit()

I get this:

Traceback (most recent call last):
  File "D:\MyProjects\com_interop\mdp_posttax.py", line 129, in <module>
    Main()
  File "D:\MyProjects\com_interop\mdp_posttax.py", line 122, in Main
    excel_com.Open()
  File "D:\MyProjects\com_interop\mdp_posttax.py", line 95, in Open
    self.workbook = self.xls_app.Workbooks.Open(self.xls_file)
_ctypes.COMError: (-2147352568, 'Bad variable type.', (None, None, None, 0, None))

I know I can use the "dynamic" keyword to make it work, like this:

self.xls_app = comtypes.client.CreateObject("Excel.Application", dynamic=True)

However, by doing that I lose access to all events that I would like to track:

self.xls_app = comtypes.client.CreateObject("Excel.Application", dynamic=True)
self.workbook = self.xls_app.Workbooks.Open(self.xls_file)

self.connection = comtypes.client.GetEvents(self.xls_app, self.event_sink)

Traceback (most recent call last):
  File "D:\MyProjects\com_interop\mdp_posttax.py", line 126, in <module>
    Main()
  File "D:\MyProjects\com_interop\mdp_posttax.py", line 119, in Main
    excel_com.Open()
  File "D:\MyProjects\com_interop\mdp_posttax.py", line 96, in Open
    self.connection = comtypes.client.GetEvents(self.xls_app, self.event_sink)
  File "C:\Python27\lib\site-packages\comtypes\client\_events.py", line 201, in GetEvents
    interface = FindOutgoingInterface(source)
  File "C:\Python27\lib\site-packages\comtypes\client\_events.py", line 81, in FindOutgoingInterface
    raise TypeError("cannot determine source interface")
TypeError: cannot determine source interface

I have tried deleting the gen folder, cleaning it up, to no avail... Any suggestion?

Thank you.

Andrea.

[den-run-ai]

Is your Excel 64-bit as well?

On Fri, Nov 24, 2017, 4:23 AM Andrea Gavana [email protected] wrote:

Hi,

I am using comtypes 1.1.3 on Windows 7 64bit, Python 2.7.3 64bit. Using this code:

import comtypes.client XLS = r"D:\Working\ExcelFile.xlsm"

xls_app = comtypes.client.CreateObject("Excel.Application") xls_app.Workbooks.Open(XLS, False, False) xls_app.Quit()

I get this:

Traceback (most recent call last): File "D:\MyProjects\com_interop\mdp_posttax.py", line 129, in Main() File "D:\MyProjects\com_interop\mdp_posttax.py", line 122, in Main excel_com.Open() File "D:\MyProjects\com_interop\mdp_posttax.py", line 95, in Open self.workbook = self.xls_app.Workbooks.Open(self.xls_file) _ctypes.COMError: (-2147352568, 'Bad variable type.', (None, None, None, 0, None))

I know I can use the "dynamic" keyword to make it work, like this:

self.xls_app = comtypes.client.CreateObject("Excel.Application", dynamic=True)

However, by doing that I lose access to all events that I would like to track:

self.xls_app = comtypes.client.CreateObject("Excel.Application", dynamic=True)self.workbook = self.xls_app.Workbooks.Open(self.xls_file) self.connection = comtypes.client.GetEvents(self.xls_app, self.event_sink)

Traceback (most recent call last): File "D:\MyProjects\com_interop\mdp_posttax.py", line 126, in Main() File "D:\MyProjects\com_interop\mdp_posttax.py", line 119, in Main excel_com.Open() File "D:\MyProjects\com_interop\mdp_posttax.py", line 96, in Open self.connection = comtypes.client.GetEvents(self.xls_app, self.event_sink) File "C:\Python27\lib\site-packages\comtypes\client_events.py", line 201, in GetEvents interface = FindOutgoingInterface(source) File "C:\Python27\lib\site-packages\comtypes\client_events.py", line 81, in FindOutgoingInterface raise TypeError("cannot determine source interface") TypeError: cannot determine source interface

I have tried deleting the gen folder, cleaning it up, to no avail... Any suggestion?

Thank you.

Andrea.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/enthought/comtypes/issues/136, or mute the thread https://github.com/notifications/unsubscribe-auth/AHgZ5ZwNq7DF-335wM0bcqDHAm1GfYHYks5s5pkKgaJpZM4QpoOb .

[jesse-git]

I'm pretty new to comtypes, but GetEvents takes a 3rd optional parameter where you can specify the interface. Here's some pseudocode:

from comtypes.gen import Excel
interface = Excel.ISomeInterface
comtypes.client.GetEvents(self.xls_app, self.event_sink, interface)

Just a guess.

[junkmd]

@infinity77 @denfromufa @jesse-git

self.xls_app = comtypes.client.CreateObject("Excel.Application", dynamic=True)

If CreateObject(... , dynamic=True), the return instance will be lazybind.Dispatch and comtypes will not automatically find the appropriate interface.

As @jesse-git pointed out, you need to specify the interface to pass to GetEvents or set the CreateObject argument to dynamic=False (which is the default value, so you can also specify no argument).

See GetEvents code.

https://github.com/enthought/comtypes/blob/7e90e54d2131b35edef3e06f98f26a255f3dfa42/comtypes/client/_events.py#L191-L203

This issue has not been active for a long time and I think it is resolvable with the points made above, so I will close it.

If the issue remains, please re-open.