How to connect Python to an open Excel workbook, using win32?

Question

I have multiple Excel instances running and want to connect to a particular file that is open in one of them. I tried following the instructions by Tim Golden to get the workbook by its full filename. This is what I did in iPython:

import win32com.client as win32
xl1 = win32.Dispatch("Excel.Application")
xl1.Visible = False
wb1 = xl1.Workbooks.Add()
wb1.SaveAs(r"C:\Users\[...]\test.xlsx")
xl2 = win32.DispatchEx("Excel.Application")
wb2 = win32.GetObject(r"C:\Users\[...]]\test.xlsm")

The result is:

Traceback (most recent call last)
<ipython-input-22-471d068eb257> in <module>
----> 1 wb2 = win32.GetObject(r"C:\Users\[...]\test.xlsm")

c:\users\[...]\venv\lib\site-packages\win32com\client\__init__.py in GetObject(Pathname, Class, clsctx)
     70     return GetActiveObject(Class, clsctx)
     71   else:
---> 72     return Moniker(Pathname, clsctx)
     73
     74 def GetActiveObject(Class, clsctx = pythoncom.CLSCTX_ALL):

c:\users\[...]\venv\lib\site-packages\win32com\client\__init__.py in Moniker(Pathname, clsctx)
     85     Python friendly version of GetObject's moniker functionality.
     86   """
---> 87   moniker, i, bindCtx = pythoncom.MkParseDisplayName(Pathname)
     88   dispatch = moniker.BindToObject(bindCtx, None, pythoncom.IID_IDispatch)
     89   return __WrapDispatch(dispatch, Pathname, clsctx=clsctx)

com_error: (-2147221014, 'Moniker cannot open file', None, None)

What am I doing wrong?

Well, that's interesting. I'd take a stab at looking at the ROT (running object table) to see what the moniker for the workbook is. There is a tool in the Windows SDK or maybe Visual Studio to display what is in the ROT. In your example, theoretically you don't need to call GetActiveObject() because that object is already loaded as the wb1 object. Do you get the same results if you make sure the workbook is closed and Excel is closed and fully shut down? (no running instances) — Joseph Willcoxson
– Joseph Willcoxson, Commented Apr 21, 2021 at 15:24
@JosephWillcoxson If I close wb1 and quit xl1 I can then get wb2 using GetObject and the full path. No issue in that case, but its not a solution. Does it teill you something? — Fredrik
– Fredrik, Commented Apr 21, 2021 at 16:08
Tells me that it doesn't like a live object already in your process space. Another test would be to get back to baseline with your Python script not running and all Excel processes shutdown. Then, open the workbook in Excel and see if you can GetActiveObject() with it. — Joseph Willcoxson
– Joseph Willcoxson, Commented Apr 21, 2021 at 16:16
@JosephWillcoxson If I could find the moniker - how would I use it to access the workbook? — Fredrik
– Fredrik, Commented Apr 21, 2021 at 16:36
@JosephWillcoxson Also - After exiting Python and opening the Workbook in Excel, I can access the same workbook using GetObject(). — Fredrik
– Fredrik, Commented Apr 21, 2021 at 17:00

Joseph Willcoxson · Accepted Answer · 2021-04-23 15:56:41Z

2

The following code works fine on my machine (Windows 10 64 bit, Python Anaconda 3.6.4). In your example, the files you used had different names. IDK if that was the problem ?

import win32com.client as win32
FileName = r"c:\toolbox\erase\auto.xlsx"
xl1 = win32.Dispatch("Excel.Application")
xl1.Visible = False
wb1 = xl1.Workbooks.Add()
wb1.SaveAs(FileName)
xl2 = win32.DispatchEx("Excel.Application")
wb2 = win32.GetObject(FileName)
print(wb2.Name)
xl1.Quit()

answered Apr 23, 2021 at 15:56

Joseph Willcoxson

6,0881 gold badge18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Fredrik Over a year ago

Oops! Looks like a typo... Will confirm asap!

Fredrik Over a year ago

Confirmed! That should teach me to obey DRY! Anyhow - thanks for spotting it. Now I can use this to unittest my vba code without having to close the workbook everytime I want to run it through the loops.

Joseph Willcoxson Over a year ago

Don't know the acronym DRY ... how many people read it and didn't see the typo? It's easy to do when it's not syntax. Sometimes for something like this, it's enlightening to run under procmon utility. It would probably recognize that it couldn't find the file.

Fredrik Over a year ago

Don’t Repeat Youself (DRY) - I should have use a variable like you did!

Collectives™ on Stack Overflow

How to connect Python to an open Excel workbook, using win32?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related