Use Python to Inject Macros into Spreadsheets

This is the code converted. You can use either the win32com or comtypes packages.

import osimport sys# Import System librariesimport globimport randomimport resys.coinit_flags = 0 # comtypes.COINIT_MULTITHREADED# USE COMTYPES OR WIN32COM#import comtypes#from comtypes.client import CreateObject# USE COMTYPES OR WIN32COMimport win32comfrom win32com.client import Dispatchscripts_dir = "C:\\scripts"conv_scripts_dir = "C:\\converted_scripts"strcode = \'''sub test()   msgbox "Inside the macro"end sub'''#com_instance = CreateObject("Excel.Application", dynamic = True) # USING COMTYPEScom_instance = Dispatch("Excel.Application") # USING WIN32COMcom_instance.Visible = True com_instance.DisplayAlerts = False for script_file in glob.glob(os.path.join(scripts_dir, "*.xls")):    print "Processing: %s" % script_file    (file_path, file_name) = os.path.split(script_file)    objworkbook = com_instance.Workbooks.Open(script_file)    xlmodule = objworkbook.VBProject.VBComponents.Add(1)    xlmodule.CodeModule.AddFromString(strcode.strip())    objworkbook.SaveAs(os.path.join(conv_scripts_dir, file_name))com_instance.Quit()

As I also struggled some time to get this right, I will provide another example which is supposed to work with Excel 2007/2010/2013's xlsm format. There is not much difference to the example provided above, it is just a little bit more simple without the looping over different files and with more comments included. Besides, the macro's source code is loaded from a textfile instead of hard-coding it in the Python script.

Remember to adapt the file paths at the top of the script to your needs.

Moreover, remember that Excel 2007/2010/2013 only allows to store Workbooks with macros in the xlsm format, not in xlsx. When inserting a macro into a xlsx file, you will be prompted to save it in a different format or the macro will not be included in the file.

And last but not least, check that Excel's option to execute VBA code from outside the application is activated (which is deactivated by default for security reasons), otherwise, you will get an error message.To do so, open Excel and go to

File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> activate checkmark on Trust access to the VBA project object model.

# necessary importsimport os, sysimport win32com.client# get directory where the script is located_file = os.path.abspath(sys.argv[0])path = os.path.dirname(_file)# set file paths and macro name accordingly - here we assume that the files are located in the same folder as the Python scriptpathToExcelFile = path + '/myExcelFile.xlsm'pathToMacro = path + '/myMacro.txt'myMacroName = 'UsefulMacro'# read the textfile holding the excel macro into a stringwith open (pathToMacro, "r") as myfile:    print('reading macro into string from: ' + str(myfile))    macro=myfile.read()# open up an instance of Excel with the win32com driverexcel = win32com.client.Dispatch("Excel.Application")# do the operation in background without actually opening Excelexcel.Visible = False# open the excel workbook from the specified fileworkbook = excel.Workbooks.Open(Filename=pathToExcelFile)# insert the macro-string into the excel fileexcelModule = workbook.VBProject.VBComponents.Add(1)excelModule.CodeModule.AddFromString(macro)# run the macroexcel.Application.Run(myMacroName)# save the workbook and closeexcel.Workbooks(1).Close(SaveChanges=1)excel.Application.Quit()# garbage collectiondel excel

What @Dirk and @dilbert mentioned works great. You can also add this piece of code which will programmatically enable access to the `VBA object module'

import win32apiimport win32conkey = win32api.RegOpenKeyEx(win32con.HKEY_CURRENT_USER,                            "Software\\Microsoft\\Office\\16.0\\Excel"                            + "\\Security", 0, win32con.KEY_ALL_ACCESS)win32api.RegSetValueEx(key, "AccessVBOM", 0, win32con.REG_DWORD, 1)