Python Add Macro to an existing XLSM

Question

Well. I've a complete Macro xlsm data. I must add a Workbook_Open vba script to the workbook. But with the lower code its just possible to add a code to the modul section. I must add the vba code to the 'this workbook' section.

Well. Its maybe better to a the vbaProject.bin to the xlsm data. But i think this is just possible with the xlswriter lib. And this lib can't add vba to existing xlsm :(

Can anyone help me ?

# set file paths and macro name accordingly - here we assume that the files are located in the same folder as the Python script
pathToExcelFile = 'Dir/test.xlsx'
pathToMacro  = 'Dir/test/macro.txt'
myMacroName = 'UsefulMacro'

# read the textfile holding the excel macro into a string
with open (pathToMacro, "r") as myfile:
    print('reading macro into string from: ' + str(myfile))
    macro=myfile.read()

print(macro)


# open up an instance of Excel with the win32com driver
excel = win32com.client.Dispatch("Excel.Application")

# do the operation in background without actually opening Excel
excel.Visible = False

# open the excel workbook from the specified file
workbook = excel.Workbooks.Open(Filename=pathToExcelFile)

# insert the macro-string into the excel file
excelModule = workbook.VBProject.VBComponents.Add(1)
excelModule.CodeModule.AddFromString(macro)

# run the macro
#excel.Application.Run(myMacroName)
xlOpenXMLWorkbookMacroEnabled = 52

# save the workbook and close
#excel.Workbooks(1).Close(SaveChanges=1)
excel.Workbooks(1).SaveAs('testneu.xlsm', FileFormat=xlOpenXMLWorkbookMacroEnabled)
#wb.SaveAs(filename, FileFormat=xlOpenXMLWorkbookMacroEnabled)
excel.Application.Quit()

# garbage collection
del excel

Answer 1

Try this code:

import win32com.client

xl = win32com.client.Dispatch("Excel.Application")

code = 'Private Sub Workbook_Open() \n \
            MsgBox "Hello!" \n \
        End Sub'

path = r'c:\Users\Alex20\Documents\test.xlsm'
wb = xl.Workbooks.Open(path)
wb.VBProject.VBComponents(1).CodeModule.AddFromString(code)
#xl.Visible = True
wb.Close(True)
xl.Quit()

Before:

After:

Running: