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:

Display the pandas dataframe in excel
Add & run VBA macro
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?

Do you need to add the code to the workbook in question? Otherwise you should be able to have the code in a separate (and probably permanently existing?) .xlsm and modify your .xlsx from there. — Inarion
– Inarion, Commented Apr 17, 2018 at 6:36
I thought about that - this is what it will boil down to in case the answer to this question here turns out to be "NO" — sudonym
– sudonym, Commented Apr 17, 2018 at 6:53
Also, what created VBA_script.bin, is there a Python library to do this? — S Meaden
– S Meaden, Commented Apr 17, 2018 at 9:56

Ricardo A · Accepted Answer · 2018-07-12 22:57:18Z

5

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

edited Jul 12, 2018 at 22:57

answered Jul 12, 2018 at 22:50

Ricardo A

1,8151 gold badge14 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sudonym Over a year ago

Killer. Thanks for your answer - I will test this ASAP and upvote if working

collinsmarra Over a year ago

is there a tools that is available to work with linux? I would like to add a vba script to my excel for example.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related