How to stop pop when calling macro from python

Question

I have macro code in an excel file and I call this macro using a python code but everytime I call this code it gives me popup(below) which I want avoid as the code is executed from remote machine and the excel file popup is not accessible to the user.

Code:

import win32com.client
def insert_column():
        fName= 'F:\\Reports\\Logs\\PERF_RESULTS.xlsm'
        xlApp = win32com.client.Dispatch("Excel.Application")
        fTest = xlApp.Workbooks.Open(fName,ReadOnly=1)
        macName = fTest.Name + '!' +'Insert_Column'
        xlApp.Application.Run(macName)
        xlApp.Application.Save()
        xlApp.Application.Quit()
        xlApp = None

Not sure exactly how to do it from Python, but in Excel VBA you would use `Application.DisplayAlerts = False` before the `Open` to suppress such popups. — John Coleman, Mar 16 '16 at 13:11
@JohnColeman That solved my issue, you should post this as an answer:) — siddhu619, Mar 16 '16 at 13:30

score 1 · Accepted Answer · answered Mar 16 '16 at 13:50

1

The Excel Application object has a DisplayAlerts property that can be set equal to False. I'm not sure of the exact syntax that you would use in calling it from Python but something like

xlApp.DisplayAlerts = False

prior to the line

fTest = xlApp.Workbooks.Open(fName,ReadOnly=1)

should suppress that popup.

answered Mar 16 '16 at 13:50

John Coleman

51,337
7
54
119

xlApp.DisplayAlerts = False wont work for run time errors – Ritesh Karwa Mar 30 '17 at 17:42
@RiteshKarwa That seems somewhat mysterious. Something must have changed, though just what might be hard to track down. Do you know what line triggers the pop-up? Is it the `Application.Run()` or the `Application.Save()`? You could try moving `.Save()` from the `Application` object to the workbook object and perhaps using `SaveAs()` rather than `Save` (see this: http://stackoverflow.com/q/14634453/4996248) . It has been over a year. You can either edit this question or ask a new question about why something that worked for a year suddenly stopped working. – John Coleman Mar 30 '17 at 18:44
yup it is the Application.Run() and so the python program gets stuck there until i close the error window – Ritesh Karwa Mar 30 '17 at 18:54
Could you modify the VBA code that is in the `Insert_Column` macro (which must be saving a file), or is it out of your control? If it is the latter, perhaps a work-around would be for Python to delete the file with the given name before you invoke `Application.Run()` (assuming that it is safe to delete this file, it might be if it is over-written anyways. The only thing to check is to make sure that the sub you are running doesn't use that file before writing over it.) – John Coleman Mar 30 '17 at 19:15
The error is going to be generated, it is part of the test but the whole goal is for python to run excel macros silently. I am assuming if thevba pop up window doesn't get generated my code will continue to run and then I can kill the whole excel.exe – Ritesh Karwa Mar 31 '17 at 01:15

How to stop pop when calling macro from python

1 Answers1