Error while converting from xls to xlsx using win32com. This program throws error if i work with other excel sheet

Question

import pandas as pd
import os
import win32com.client
import win32com.client.gencache
fname = "C:\\Users\\prashanth\\Desktop\\student.xls"
excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(fname)
wb.SaveAs(fname+"x", FileFormat = 51)    
wb.Close()                               
excel.Application.Quit()

Traceback (most recent call last): File "c:\users\prashanth\appdata\local\programs\python\python36-32\lib\site-packages\win32com\client\gencache.py", line 536, in EnsureDispatch ti = disp.oleobj.GetTypeInfo() pywintypes.com_error: (-2147418111, 'Call was rejected by callee.', None, None)

During handling of the above exception, another exception occurred:

Traceback (most recent call last): File "dailyreports2.py", line 6, in excel = win32com.client.gencache.EnsureDispatch('Excel.Application') File "c:\users\prashanth\appdata\local\programs\python\python36-32\lib\site-packages\win32com\client\gencache.py", line 547, in EnsureDispatch raise TypeError("This COM object can not automate the makepy process - please run makepy manually for this object") TypeError: This COM object can not automate the makepy process - please run makepy manually for this object

Answer 1

I got the same error after these lines:

import win32com.client
import win32com.client.gencache
xl = win32com.client.gencache.EnsureDispatch('Excel.Application')

A machine restart worked for me.

Answer 2

It has to do with the automated creation of folder of python scripts related to the com object. C:\Users\[username]\AppData\Local\Temp\gen_py

When you EnsureDispatch, that folder is created every time through makepy. When you Dispatch, it references a existing folder.

I'm dealing with the same issue and sometimes deleting the contents of 3.7 in the gen_py folder works. I'm still wrapping my head around it but am busy at work

More info can be found in this thread