0

I have a pandas dataframe df. I then save it as an excel file Report.xlsm and then I add VBA code to filter this data.

The code runs well. However, when I execute the VBA code, Excel always shows the message box to ask to save the Report.xlsm. Does anyone know how to get rid of this message and automatically save it, rahter than having to click save?

If I do not click save, the excel file will be broken and the code will be crash too.

Microsoft Excel, ask to save file notification

Python Code:

desktop = os.path.normpath(os.path.expanduser("~/Desktop"))

# Generate excel file
writer = pd.ExcelWriter(desktop + '\Report.xlsx', engine = 'xlsxwriter')
df.to_excel(writer, index = False, sheet_name = 'Report')

workbook = writer.book
workbook.filename = desktop + '\Report.xlsm'

# Add VBA code to new excel report - Report.xlmx  from vbaProject.bin  
workbook.add_vba_project('vbaProject.bin')
writer.save()

# RUN MACRO
exel = win32com.client.Dispatch('Excel.Application')

exel.Workbooks.Open(Filename = desktop + '\Report.xlsm' , ReadOnly=1)
xl.Application.Run("Macro1")          

xl.Application.Quit()
del xl
RTbecard
  • 868
  • 1
  • 8
  • 23
tinpham36
  • 1
  • Can you fix the formatting of your text and code? – AMC Feb 07 '20 at 17:13
  • @AMC I just edited the question (pending review) to fix the formatting issues. @tinpham36, don't forget to add your `imports` to your python script shown here. We can assume `pd` refers to `pandas`, but we shouldn't have to :p – RTbecard Feb 07 '20 at 17:20

1 Answers1

1

Add this at the end of your code

exel.ActiveWorkbook.Close(True)

True = Save, False = Do Not Save

SofiaChalk
  • 11
  • 4