Can python add, run & remove a VBA macro to excel without intermediate saving steps?

Question

I have a long-running python pipeline that generates a pandas dataframe. Briefly, I want to:

1. Display the pandas dataframe in excel
2. Add & run VBA macro
3. Remove VBA macro and save the (newly formatted) output as .xslx

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?

Answer 1

Yes, create the instance, use the built in VBA Code Import, run macro, save as xlsx to remove macro.

xl = win32com.client.Dispatch("Excel.Application")
xl.Workbooks.Add
wb = xl.ActiveWorkbook
wb.VBProject.VBComponents.Import "Full Path\VBA_script.bin"
xl.Application.Run("Module1.Main")
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