Python vba extract to get bin of macro

Question

I am trying to add a vba_project to "Sheet1" of a workbook using python.

I am following XLSXWRITER documentation to get the bin of the VBA code from a different sheet which I would want to use in "Sheet1" of my new workbook.

I enter the below code in command prompt but I get the error: "'vba_extract.py' is not recognized as an internal or external command"

$ vba_extract.py Book1.xlsm
Extracted: vbaProject.bin

Can someone give me a step by step on how to extract the macro from old file as bin and then input into sheet1 of new workbook using python?

Answer 1

I figured this out today and just wanted to leave it here for any future people to use. This was so unbelievably frustrating to figure out how to do. If you are using the Pandas library, this is also relevant. Make sure to install xlsxwriter also.

1.Click on your windows start button and type 'cmd' and click on it to run the Command Prompt.

2.Once you have it open, you need to locate where the vba_extract.py file is. For me it was here:

C:\Users\yourusername\AppData\Local\Programs\Python\Python36-32\Scripts\vba_extract.py

3.Now, you need to get the path of the .xlsm file you want to take from. If you don't have a .xlsm file made. Make one. Here is an example:

C:\Users\yourusername\Desktop\excelfilename.xlsm

4.Now, back to the Command Prompt. This is exactly what you will type. You will take both items from steps 2 and 3 and combine then and hit enter. Here:

C:\Users\yourusername\AppData\Local\Programs\Python\Python36-32\Scripts\vba_extract.py C:\Users\yourusername\Desktop\excelfilename.xlsm

if it is successful, it will tell you this:

Extracted: vbaProject.bin

5.For this one I'm not sure. I assume that wherever your .xlsm file is where the .bin file will end up. For this example, it ended up on my desktop. It will have all the macros you created or had on the original .xlsm file.

C:\Users\yourusername\Desktop/vbaProject.bin

Here is an example of it being used in full code:

import pandas
import xlsxwriter

df_new = pd.read_csv('C:\\Users\\yourusername\\Desktop\\CSV1.csv')
writer = pd.ExcelWriter('C:\\Users\\yourusername\\Desktop\\CSV1.xlsx')
df_new.to_excel(writer, index = False, sheet_name = 'File Name', header = False)
pandaswb = writer.book
pandaswb.filename = 'C:\\Users\\yourusername\\Desktop\\newmacroexcelfile.xlsm')
pandaswb.add_vba_project(r'C:\Users\yourusername\Desktop/vbaProject.bin')
writer.save()