CODEWITHSUNDEEP
pythonexcelwin32com
Chris
Chris

Reputation: 530

python-win32com excel com model started generating errors

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)

Upvotes: 33

Views: 63910

Answers (7)

Harkan
Harkan

Reputation: 192

I've tried deleting and re-importing win32com, but it seems a reference to the corrupted cache is still maintained, so the whole process needs to be restarted.

To add to Daynil's answer, I managed to automate the recovery process without restarting the whole process. I digged through the win32com.client.gencache code, and it looks like you only need to delete the sys.modules reference to the problematic module to make gencache rebuild the files on the second Dispatch.

Here is my proposed code:

import win32com.client, shutil
from pathlib import Path

for attempt in range(2):
        try:
            excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
            
        except AttributeError as e:
            if e.name == 'CLSIDToClassMap':
                mod_name = e.obj.__name__
                mod_name_parts = mod_name.split('.')
                if len(mod_name_parts) == 3:
                    # Deleting the problematic module cache folder
                    folder_name = mod_name_parts[2]
                    gen_path = Path(win32com.__gen_path__)
                    folder_path = gen_path.joinpath(folder_name)
                    shutil.rmtree(folder_path)
                    # Deleting the reference to the module to force a rebuilding by gencache
                    del sys.modules[mod_name]
                    continue

            raise Exception("There was an error loading Excel.") from e

Upvotes: 2

Ian
Ian

Reputation: 1033

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__)

or, even better, a one-liner in the command line:

python -c "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.

💡Note that this directory is usually in your "TEMP" directory (copy-paste %TEMP%/gen_py in Windows File Explorer and you will arrive there directly).

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.

Upvotes: 64

Qin Heyang
Qin Heyang

Reputation: 1674

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.

💡TIP: You can also put in your Windows file explorer %TEMP%\gen_py to access it directly, and then delete its content.

Upvotes: 20

Daynil
Daynil

Reputation: 1846

To add to this discussion, for those receiving this error as part of an unsupervised automated process, you can fully automating the recovery process and allow any processes to continue unsupervised.

As answers by Ian and Qin mention, we need to delete the gen_py output directory and restart the process. As far as automation goes, there are two problems with this: the gen_py output directory is a temporary directory that can change, and currently running processes that rely on gen_py continue to fail even after it is regenerated.

To address these problems, we can dynamically look up the location, then we can completely nuke the whole process and restart it. I've tried deleting and re-importing win32com, but it seems a reference to the corrupted cache is still maintained, so the whole process needs to be restarted.

temp_data_dir = os.environ.get('LOCALAPPDATA'))
gen_py_dir = ''
for curr_path, dirs, files, in walk(temp_data_dir)
    if 'gen_py' in dirs:
        gen_py_dir = Path(curr_path).joinpath('gen_py')

shutil.rmtree(gen_py_dir)
execv(restart_args)

The restarted process should call win32com.client.gencache.EnsureDispatch('Excel.Application') again and a fresh copy of gen_py will now be utilized, then we can retry the failed Excel automation code.

The issue happens sporadically in a completely unreliable way that I can't replicate. My best guess is that the gen_py cache is sometimes corrupted somehow and just needs to be refreshed.

Upvotes: 0

Julienm
Julienm

Reputation: 188

Execute this command line in a powershell or cmd (NOT in Administrator mode => wouldn't work for me)

   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.

If still not working, reinstall pywin32 of your python distribution

<path to python root or venv>\pip.exe uninstall pywin32
<path to python root or venv>\pip.exe install pywin32

Upvotes: 11

Nikita Potemkin
Nikita Potemkin

Reputation: 63

What has worked for me is:

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

Upvotes: 3

Griffin
Griffin

Reputation: 14634

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.

Upvotes: 1

Related Questions