Can python add, run & remove a VBA macro to excel without intermediate saving steps?
I have a long-running python pipeline that generates a pandas dataframe. Briefly, I want to:
- Display the pandas dataframe in excel
- Add & run VBA macro
- Remove VBA macro and save the (newly formatted) output as
The challenge is that I can't figure out how to do this without an intermediate save-as-
xlsm-file step, which is apparently required when adding a VBA macro to an
.xlsx file. Since this is inefficient, I want to get rid of this intermediate step.
Here is the code:
1. Display the pandas dataframe in excel:
with pd.ExcelWriter('output.xlsx') as writer: df_results.to_excel(writer, index = False, sheet_name = "Sheet1") #...see below
2A. Add macro and assign .xlsm-filename so that writer can hold macro
#... see above writer.book.filename = 'output.xlsm' # Add .xlsm filename writer.book.add_vba_project('VBA_script.bin') # This adds my macro writer.save() # How to get rid of this step?
2B. Run macro
xl = win32com.client.Dispatch("Excel.Application") # Set up excel xl.Workbooks.Open(Filename = 'output.xlsm') # Open .xlsm file from step 2A xl.Application.Run("Module1.Main") # Run VBA_macro.bin
3. Remove macro and save to .xlsx
wb = xl.ActiveWorkbook xl.DisplayAlerts = False wb.DoNotPromptForConvert = True wb.CheckCompatibility = False wb.SaveAs('final_outfile.xlsx', FileFormat=51, ConflictResolution=2) # Macro disapears here xl.Application.Quit() del xl xl = None
Is it possible to do this without intermediate .xlsm step & with shorter code?
How to add, run & remove a VBA macro in an excel context without intermediate saving steps using python?
Edit: It seems the answer is "No." - people recommend to use a second, permanent
.xlsm container to hold the VBA macro - If you disagree, feel free to answer accordignly