2

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:

  1. Open an Excel Application. This will be shown as Book1 - Excel in the title by default
  2. 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
beachgreatsquaremoon
  • 87
  • 1
  • 14
  • This is not the right way to control Excel. Excel has an extensive API to control their object model, but that's done through COM, using the `win32com` module. Here are some examples. https://pythonexcels.com/python/2009/10/05/python-excel-mini-cookbook – Tim Roberts Nov 07 '22 at 18:16
  • The reason I am trying this is beacuse there is another process which is generating this excel on screen. – beachgreatsquaremoon Nov 07 '22 at 18:18
  • I know. `win32com.client.Dispatch('Excel.Application')` will connect to an existing instance, if there is one. – Tim Roberts Nov 07 '22 at 18:38
  • It is able to connect but cant save. I was able to connect with pywinauto as well. – beachgreatsquaremoon Nov 07 '22 at 19:25
  • 1
    **but cant save** -- Show us your code. `Dispatch` returns an Application object. From that, you can fetch the first workbook from the Workbooks collection, and the workbook object has a `SaveAs` method. – Tim Roberts Nov 07 '22 at 20:02
  • app = win32.Dispatch('Excel.Application') Does the app object have a method to return the first workbook? – beachgreatsquaremoon Nov 08 '22 at 04:37
  • 1
    Have you checked any of the documentation? Because it doesn't feel like you have. https://learn.microsoft.com/en-us/office/vba/api/excel.application.activeworkbook – Tim Roberts Nov 08 '22 at 06:54
  • It's possible to save it by pywinauto, but COM objects is more right way to do it. Something like `app.Workbooks(1).SaveAs("filename.xlsx")` should help. – Vasily Ryabov Nov 08 '22 at 08:35
  • @TimRoberts I have tried this and it gives me an error AttributeError: Excel.Application.ActiveWorkbook app = win32.Dispatch('Excel.Application') app.ActiveWorkbook.SaveAs(r"C:\Users\.....\Desktop\......xlsx") – beachgreatsquaremoon Nov 08 '22 at 16:23
  • @VasilyRyabov This version raises another error AttributeError: Excel.Application.Workbooks app = win32.Dispatch('Excel.Application') app.Workbooks(1).SaveAs(r"C:\Users\....\Desktop\.....xlsx") But i see that its able to save an empty excel. However if I add some data and run the code, it fails – beachgreatsquaremoon Nov 08 '22 at 16:25
  • 2
    Fails how? It may be time to post a new question. – Tim Roberts Nov 08 '22 at 17:54
  • @TimRoberts This is the code i tried and it worked for the first time. Later it started throwing me an error. `TypeError: This COM object can not automate the makepy process - please run makepy manually for this object` This is the code - `app = win32.gencache.EnsureDispatch('Excel.Application') app.Workbooks(1).SaveAs(r"C:\Users\...\Desktop\....xlsx") app.Application.Quit()` – beachgreatsquaremoon Nov 10 '22 at 16:22
  • And did you run makepy? – Tim Roberts Nov 10 '22 at 18:39
  • Not yet, still figuring out how to. – beachgreatsquaremoon Nov 11 '22 at 16:13
  • I ran your code and it worked for me. The file was saved. No error messages. BUT, I changed the path to a path on my machine. Do you really have a user named `test`? on your machine? – G5W Nov 15 '22 at 21:19
  • A related answer https://stackoverflow.com/questions/57030278/typeerror-this-com-object-can-not-automate-the-makepy-process-please-run-make suggests you may be running the Excel application on your machine – henrycjc Nov 16 '22 at 09:30

3 Answers3

0

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 .

wallbloggerbeing
  • 105
  • 9
  • This is the full code. No other loops. And it's a licensed version. The problem is not with excel. Also I would suggest you to try the given code and check. I have mentioned the steps to reproduce the error. – beachgreatsquaremoon Nov 16 '22 at 11:39
0

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

scotscotmcc
  • 2,719
  • 1
  • 6
  • 29
  • I have also tried this version too which calls Dispatch directly. The problem with this is, it doesn't work the 2nd time. Try it yourself. – beachgreatsquaremoon Nov 16 '22 at 16:48
  • What do you mean 'the second time'? In this code we've closed the workbook and quit from Excel, and so there is no longer a 'Book1'. Instead, there is the file we saved, which we could reopen if we wanted to do something to it again. Or just not close/quit and keep doing things? or is your issue trying to save to the same location again? – scotscotmcc Nov 16 '22 at 17:07
  • This is not a one time thing. Another application will open the book1 again. And that needs to be saved too. Repeat the same activity and check the error. – beachgreatsquaremoon Nov 16 '22 at 17:16
  • When you save the workbook, there ceases to be a 'Book1' because it gets the name you saved it to (in this case, 'new_xl.xlsx'). So if I run the code up to `SaveAs()` (don't run close/quit) and I do xl.Workbooks['Book1'], that workbook doesn't exist. However, I can do xl.Workbooks['new_xl.xlsx'] and that will indeed return the workbook. – scotscotmcc Nov 16 '22 at 17:20
  • That's it fine. But whatever I am saying is the 2nd run of this code. Once you have fully executed this once, saved the files and closed the Excel. Repeat the steps again in the question. – beachgreatsquaremoon Nov 16 '22 at 17:23
  • I just opened Excel, ran the original code I had there (just a single save), and then opened Excel and ran the code again. I did not get your error. I did have to deal with Excel had a pop-up saying something like 'there is already a file saved here, do you want to overwrite?'. Is that the problem you are having? – scotscotmcc Nov 16 '22 at 17:27
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/249674/discussion-between-beachgreatsquaremoon-and-scotscotmcc). – beachgreatsquaremoon Nov 16 '22 at 17:28
  • Hi @scotscotmcc, I did multiple tests on this. looks like it runs a few times and then starts failing continuously. And to fix it back I have to restart my system! The erro I am getting is this ```pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2147352565), None)``` – CoderWithAGoodName Nov 22 '22 at 17:44
  • @CoderWithAGoodName, i'd recommend opening a new question that fully explains your issue, has your code etc. feel free to tag me on it in the comments or something like that. – scotscotmcc Nov 22 '22 at 17:56
0

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()
beachgreatsquaremoon
  • 87
  • 1
  • 14