How to add "Microsoft Excel Object" VBA code to Excel file using Python

Question

I have a long script written in VBA, in Excel. I'm using Python to create another Excel file, and I want to embed the script in that file. I have been able to add a macro as "Module1" in Excel, but not as a "Microsoft Excel Object" associated with a specific worksheet. It has to be associated with one named worksheet, because it highlights particular cells when you click on input cells.

I've extracted the vbaProject.bin into the same folder as the Excel file I want to add it to. I have also remembered to set the correct permissions in Excel (File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Trust Access to the VBA Project Object Model).

I've just generated a file called output.xlsm that opens without issue, contains no VBA, and has multiple worksheets, before starting this code.

I hope this is clear - I am a newbie and this is my first question.


import os
import win32com.client

mydir = os.getcwd()

with open ('vbaProject.bin', 'rb') as myfile:
    print('Reading macro into string from vbaProject.bin')
    macro = myfile.read()

excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = False
workbook = excel.Workbooks.Open(Filename = mydir + '/output.xlsm')
excelModule = workbook.VBProject.VBComponents.Add(1)
excelModule.CodeModule.AddFromString(macro)
excel.Workbooks(1).Close(SaveChanges = 1)
excel.Application.Quit()
del excel

This code adds "Module1" rather than a Microsoft Excel Object.

Also, when I try to close the new Excel file, the macro that I've added as "Module1" has not formed properly. It's a few gobbledegook characters, instead of a long script.

Thanks for any help you can offer.

Would it be an option to save your Excel File as template (.xltm )and get a new copy every time you run the code? In case you need a different name for the Worksheet you can simply rename it afterwards. — L8n
L8n - I think that would be okay, if you see a solution through that. Would using a template be any different than using an input xlsm with the macro preinstalled, and modifying that? That's a workaround I'm considering if I can't get this to work. But will require reengineering a fair bit of the code possibly. — SuperScienceGrl
No, you could use either, I like the way Template files are more difficult for the users to mess up. — L8n

Nacorid Nacorid · Accepted Answer · 2019-08-05T08:57:30

If you want to add code to a specific, already existing sheet you need to use that sheet's CodeName as an index of the VBComponents collection.

excelModule = workbook.VBProject.VBComponents("SheetCodeName_NotSheetName")
excelModule.CodeModule.AddFromString(macro)

You could also .AddFromFile("path/to/file/filename") or .CreateEventProc("EventName", "ObjectName").
The latter returns a Long indicating the start of the EventProcedure where you can

excelModule.CodeModule.InsertLines(LineNumberWhereToInsert, "Your code as String")

However you choose to insert the code make sure it's formatted correctly. VBA uses 4 spaces for a tab, CR + LF combination on Windows and CR on Mac for new lines. And be extra careful with special characters.

How to add "Microsoft Excel Object" VBA code to Excel file using Python

1 Answers