python-win32com excel com model started generating errors

Question

Over the last few days, I have been working on automating the generation of some pivot tables for a number of reports.

Boiled down to the minimum, the following code was working without issue:

import win32com.client    
objExcelApp = win32com.client.gencache.EnsureDispatch('Excel.Application')
objExcelApp.Visible = 1

This would pop-up an instance of excel and I could continue working in Python. But suddenly, today my scripts are failing with the following:

>>>import win32com.client
>>> objExcelApp = win32com.client.gencache.EnsureDispatch('Excel.Application')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\win32com\client\gencache.py", line 534, in EnsureDispatch
    mod = EnsureModule(tla[0], tla[1], tla[3], tla[4], bForDemand=bForDemand)
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\win32com\client\gencache.py", line 391, in EnsureModule
    module = GetModuleForTypelib(typelibCLSID, lcid, major, minor)
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\win32com\client\gencache.py", line 266, in GetModuleForTypelib
    AddModuleToCache(typelibCLSID, lcid, major, minor)
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\win32com\client\gencache.py", line 552, in AddModuleToCache
    dict = mod.CLSIDToClassMap
AttributeError: module 'win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x9' has no attribute 'CLSIDToClassMap'

The code has not changed from yesterday to today. I have no idea what is happening!!!.

Another interesting kicker. if I do the same code in the same session again I get a different error:

>>> objExcelApp = win32com.client.gencache.EnsureDispatch('Excel.Application')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\win32com\client\gencache.py", line 534, in EnsureDispatch
    mod = EnsureModule(tla[0], tla[1], tla[3], tla[4], bForDemand=bForDemand)
  File "C:\Program Files (x86)\Python37-32\lib\site-packages\win32com\client\gencache.py", line 447, in EnsureModule
    if module.MinorVersion != tlbAttributes[4] or genpy.makepy_version != module.makepy_version:
AttributeError: module 'win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x9' has no attribute 'MinorVersion'
>>>

So I jump over to a windows machine with a fresh windows install, install python37 and pip install pypiwin32. Run the very same lines and excel opens just like it did yesterday on my original machine.

I tried un-installing and re-installing with no luck. Any idea what is going on here?

NOTE: Dynamic dispatch still works:

import win32com.client
objExcelApp = win32com.client.Dispatch("Excel.Application")
objExcelApp.Visible = 1

But I specifically need static dispatch as Pivot Tables won't work with a dynamically dispatched object (much later in my code):

objExcelPivotCache = objExcelWorkbook.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=objExcelPivotSourceRange)

Answer 1

I had the same issue and I resolved it by following the instructions here: https://mail.python.org/pipermail/python-win32/2007-August/006147.html

Deleting the gen_py output directory and re-running makepy SUCCEEDS and subsequently the test application runs OK again.

So the symptom is resolved, but any clues as to how this could have happened. This is a VERY long running application (think 24x7 for years) and I'm concerned that whatever caused this might occur again.

To find the output directory, run this in your python console / python session:

import win32com
print(win32com.__gen_path__)

Based on the exception message in your post, the directory you need to remove will be titled '00020813-0000-0000-C000-000000000046x0x1x9'. So delete this directory and re-run the code. And if you're nervous about deleting it (like I was) just cut the directory and paste it somewhere else.

I have no idea why this happens nor do I know how to prevent it from happening again, but the directions in the link I provided seemed to work for me.

Answer 2

A more straightforward solution was posted in a related question Issue in using win32com to access Excel file.

Basically, you just need to delete the folder C:\Users\<your username>\AppData\Local\Temp\gen_py and rerun your code.

Answer 3

Execute this command line

python -m win32com.client.makepy "Excel.Application"

It fixes all errors and you don't have to change your python code. And keep using

win32com.client.gencache.EnsureDispatch("Excel.Application")

With gencache.EnsureDispatch you have access to the constants of the application loaded dynamically by makepy which must have the registered application (in our case Excel.Application). If you have the same problem with Outlook, use "Outlook.Application" in above.

Answer 4

What has worked for me is:

excel = win32.gencache.EnsureDispatch('Excel.Application')
#change to =>
excel = win32.Dispatch('Excel.Application')

Answer 5

For me, it seems, the issue was that I have multiple processes that interact with Windows apps through win32com.

Since win32com creates the "gen_py" directory in win32api.GetTempPath() this can cause conflicts and the cache getting corrupted.

My solution is to set a custom location for "gen_py" for each process. A simple example:

from pathlib import Path
import win32com

gen_py_path = '/some/custom/location/gen_py'

Path(gen_py_path).mkdir(parents=True, exist_ok=True)
win32com.__gen_path__ = gen_py_path

# Any other imports/code that uses win32com

This way you don't have to delete the default "gen_py" folder and wonder what issues might arise. But if you still find you need to delete, you can just delete the custom folder and know you're deleting the cache just for that process.