How to save an excel using pywin32?-CodePudding

I am trying to save an excel file generated by another application that is open. i.e the excel application is in the foreground. This file has some data and it needs to be saved i.e written into the disk.

In other words, I need to do an operation like File->SaveAs.

Steps to reproduce:

Open an Excel Application. This will be shown as Book1 - Excel in the title by default
Write this code and run

import win32com.client as win32

app = win32.gencache.EnsureDispatch('Excel.Application')
app.Workbooks(1).SaveAs(r"C:\Users\test\Desktop\test.xlsx")
app.Application.Quit()

Error -

Traceback (most recent call last):
  File "c:/Users/test/Downloads/automate_excel.py", line 6, in <module>
    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 "c:/Users/test/Downloads/automate_excel.py", line 6, in <module>
    app = win32.gencache.EnsureDispatch('Excel.Application')
  File "C:\Users\test\AppData\Local\Programs\Python\Python38\lib\site-packages\win32com\client\gencache.py", line 633, in EnsureDispatch
    raise TypeError(
TypeError: This COM object can not automate the makepy process - please run makepy manually for this object

CodePudding user response：

There could be many sources for your problem so I would apreciate if you shared further code. The second error can for example occur when you are running multiple instances of the line excel = win32.gencache.EnsureDispatch('Excel.Application') for example in a for loop . Also make sure to have a version of excel that is fully activated and licensed .

CodePudding user response：

This is working for me (on python==3.9.8 and pywin32==305). You'll see that the first line is a different than yours, but I think that's really it.

In the course of this we kept getting Attribute Errors for the Workbook or for setting DisplayAlerts. We found (from this question: Excel.Application.Workbooks attribute error when converting excel to pdf) that if Excel is in a loop (for example, editing a cell or has a pop-up open) then you will get an error. So, be sure to click enter out of a cell so that you aren't editing it.

import win32com.client as win32
savepath = 'c:\\my\\file\\path\\test\\'

xl = win32.Dispatch('Excel.Application') 

wb = xl.Workbooks['Book1']
wb.DisplayAlerts = False # helpful if saving multiple times to save file, it means you won't get a pop-up for overwrite and will default to save it.
filename = 'new_xl.xlsx'
wb.SaveAs(savepath filename)
wb.Close()
xl.Quit()

edit: add pywin32 version, include some more tips

CodePudding user response：

This is the version that worked for me based on @scotscotmcc's answer. The issue was with the cell which was in edit mode while I was running the program. Make sure you hit enter in the current cell and come out of the edit mode in excel.

import win32com.client as win32
import random
xl = win32.Dispatch('Excel.Application')
wb = xl.Workbooks['Book1']
wb.SaveAs(r"C:\Users\...\Desktop\Form" str(random.randint(0,1000)) ".xlsx")
wb.Close()
xl.Quit()