I would expect the error is to do with the macro you're calling, try the following bit of code:

Code

import os, os.pathimport win32com.clientif os.path.exists("excelsheet.xlsm"):    xl=win32com.client.Dispatch("Excel.Application")    xl.Workbooks.Open(os.path.abspath("excelsheet.xlsm"), ReadOnly=1)    xl.Application.Run("excelsheet.xlsm!modulename.macroname")##    xl.Application.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.    xl.Application.Quit() # Comment this out if your excel script closes    del xl

I did some modification to the SMNALLY's code so it can run in Python 3.5.2. This is my result:

    #Import the following library to make use of the DispatchEx to run the macro    import win32com.client as wincl    def runMacro():        if os.path.exists("C:\\Users\\Dev\\Desktop\\Development\\completed_apps\\My_Macr_Generates_Data.xlsm"):        # DispatchEx is required in the newest versions of Python.        excel_macro = wincl.DispatchEx("Excel.application")        excel_path = os.path.expanduser("C:\\Users\\Dev\\Desktop\\Development\\completed_apps\\My_Macr_Generates_Data.xlsm")        workbook = excel_macro.Workbooks.Open(Filename = excel_path, ReadOnly =1)        excel_macro.Application.Run\            ("ThisWorkbook.Template2G")        #Save the results in case you have generated data        workbook.Save()        excel_macro.Application.Quit()          del excel_macro

Just a quick note with a xlsm with spaces.

file = 'file with spaces.xlsm'excel_macro.Application.Run('\'' + file + '\'' + "!Module1.Macro1")

I suspect you haven't authorize your Excel installation to run macro from an automated Excel. It is a security protection by default at installation. To change this:

1. File > Options > Trust Center
2. Click on Trust Center Settings... button
3. Macro Settings > Check Enable all macros

Hmm i was having some trouble with that part (yes still xD):

xl.Application.Run("excelsheet.xlsm!macroname.macroname")

cos im not using excel often (same with vb or macros, but i need it to use femap with python) so i finaly resolved it checking macro list: Developer -> Macros:there i saw that: thismacroname.macroname should besheet_name.macroname like in "Macros" list.

(i spend something like 30min-1h trying to solve it, so it may be helpful for noobs like me in excel) xD

A variation on SMNALLY's code that doesn't quit Excel if you already have it open:

import os, os.pathimport win32com.client    if os.path.exists("excelsheet.xlsm"):    xl=win32com.client.Dispatch("Excel.Application")    wb = xl.Workbooks.Open(os.path.abspath("excelsheet.xlsm"), ReadOnly=1) #create a workbook object    xl.Application.Run("excelsheet.xlsm!modulename.macroname")    wb.Close(False) #close the work sheet object rather than quitting excel    del wb    del xl

I tried the win32com way and xlwings way but I didn't get any luck. I use PyCharm and didn't see the .WorkBook option in the autocompletion for win32com.I got the -2147352567 error when I tried to pass a workbook as variable.

Then, I found a work around using vba shell to run my Python script. Write something on the XLS file you are working with when everything is done. So that Excel knows that it's time to run the VBA macro.

But the vba Application.wait function will take up 100% cpu which is wierd. Some people said that using the windows Sleep function would fix it.

 Import xlsxwriter Shell "C:\xxxxx\python.exe  C:/Users/xxxxx/pythonscript.py" exitLoop = 0

wait for Python to finish its work.

  Do  waitTime = TimeSerial(Hour(Now), Minute(Now), Second(Now) + 30)  Application.Wait waitTime  Set wb2 = Workbooks.Open("D:\xxxxx.xlsx", ReadOnly:=True)  exitLoop = wb2.Worksheets("blablabla").Cells(50, 1)  wb2.Close exitLoop  Loop While exitLoop <> 1  Call VbaScript

I am using Windows 22H2 Build 22621.1194 64 bit

Python 3.11.2

Microsoft® Excel® for Microsoft 365 MSO (Version 2302 Build 16.0.16130.20186) 64-bit

I neededpython -m pip install pywin32 for the win32com.client

Python code

import timeimport win32com.clientxl=win32com.client.Dispatch("Excel.Application")xl.Workbooks.Open(Filename=r"C:\Users\david\Desktop\xl_HW.xlsm",ReadOnly=1)xl.Application.Run("sbHelloWorld")xl.Workbooks(1).Close(SaveChanges=0)xl.Application.Quit()xl=0time.sleep(10)

The module name was not needed, because the subroutine was declaredPublic

and the routine in VBA is -

Public Sub sbHelloWorld()    MsgBox "Hello World"End Sub

For Python 3.7 or later,(2018-10-10), I have to combine both @Alejandro BR and SMNALLY's answer, coz @Alejandro forget to define wincl.

import os, os.pathimport win32com.clientif os.path.exists('C:/Users/jz/Desktop/test.xlsm'):    excel_macro = win32com.client.DispatchEx("Excel.Application") # DispatchEx is required in the newest versions of Python.    excel_path = os.path.expanduser('C:/Users/jz/Desktop/test.xlsm')    workbook = excel_macro.Workbooks.Open(Filename = excel_path, ReadOnly =1)    excel_macro.Application.Run("test.xlsm!Module1.Macro1") # update Module1 with your module, Macro1 with your macro    workbook.Save()    excel_macro.Application.Quit()      del excel_macro

• python
• excel
• vba
• python-2.7