All we need is an easy explanation of the problem, so here it is.
I’ve got a macro that I’d like a bunch of existing spreadsheets to use. The only problem is that there are so many spreadsheets that it would be too time consuming to do it by hand!
I’ve written a Python script to access the needed files using pyWin32, but I can’t seem to figure out a way to use it to add the macro in.
A similar question here gave this answer (it’s not Python, but it looks like it still uses COM), but my COM object doesn’t seem to have a member called VBProject:
Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.DisplayAlerts = False Set objWorkbook = objExcel.Workbooks.Open("C:\scripts\test.xls") Set xlmodule = objworkbook.VBProject.VBComponents.Add(1) strCode = _ "sub test()" & vbCr & _ " msgbox ""Inside the macro"" " & vbCr & _ "end sub" xlmodule.CodeModule.AddFromString strCode objWorkbook.SaveAs "c:\scripts\test.xls" objExcel.Quit
EDIT: Link to the similar question referenced: Inject and execute Excel VBA code into spreadsheet received from external source
I also forgot to mention that although this isn’t Python, I was hoping that similar object members would be available to me via the COM objects.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
import os import sys # Import System libraries import glob import random import re sys.coinit_flags = 0 # comtypes.COINIT_MULTITHREADED # USE COMTYPES OR WIN32COM #import comtypes #from comtypes.client import CreateObject # USE COMTYPES OR WIN32COM import win32com from win32com.client import Dispatch scripts_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 COMTYPES com_instance = Dispatch("Excel.Application") # USING WIN32COM com_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 imports import os, sys import win32com.client # get directory where the script is located _file = os.path.abspath(sys.argv) 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 script pathToExcelFile = path + '/myExcelFile.xlsm' pathToMacro = path + '/myMacro.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() # 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) # save the workbook and close excel.Workbooks(1).Close(SaveChanges=1) excel.Application.Quit() # garbage collection del 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 win32api import win32con key = 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)
I’d like to add to this. I was having trouble trying to parse through every .xlsm file in my folder. I figured out how to do it using glob and formatting a with statement inside a for statement. I am new to programming so please excuse me if I am using incorrect terminology. Hope this helps others. Thanks.
import glob import os, sys import win32com.client _file = os.path.abspath(sys.argv) path = os.path.dirname(_file) pathToMacro = path + r'\macro2.txt' myMacroName = 'TestMacro' for fname in glob.glob(path + "\*.xlsm"): with open (pathToMacro, "r") as myfile: print('reading macro into string from: ' + str(myfile)) macro=myfile.read() excel = win32com.client.Dispatch("Excel.Application") excel.Visible = False workbook = excel.Workbooks.Open(Filename=fname) excelModule = workbook.VBProject.VBComponents.Add(1) excelModule.CodeModule.AddFromString(macro) excel.Application.Run('Module1.CleanDataPivot') excel.Workbooks(1).Close(SaveChanges=1) excel.Application.Quit() del excel
Now I can run one macro through every .xlsm file in my folder without opening up excel.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂