This can be done easily through xlwings. Once I switched to that library then I was able to quickly get this script working.

First make sure you have your All.xlsm file in your current working or in your User/Documents(Sometimes it working from yourDocuments directory and sometimes not, so better to have in both)

pass your macro name along with the file name that contains the macro you can make change to Parameters like ReadOnly or Savechanges according to your requirement

And be make sure to deleta xl object after each run

import win32com.clientxl =win32com.client.dynamic.Dispatch('Excel.Application')xl.Workbooks.Open(Filename = XYZ.xls, ReadOnly= 1)xl.Application.Run('All.xlsm!yourmacroname')xl.Workbooks(1).Close(SaveChanges=1)xl.Application.Quit()del xl

Running Excel Macro from Python

To Run a Excel Marcro from python, You don't need almost nothing. Below a script that does the job. The advantage of Updating data from a macro inside Excel is that you immediatly see the result. You don't have to save or close the workbook first. I use this methode to update real-time stock quotes. It is fast and stable.This is just an example, but you can do anything with macros inside Excel.

from os import system, pathimport win32com.client as win32from time import sleepdef isWorkbookOpen(xlPath, xlFileName):    SeachXl = xlPath + "~$" + xlFileName    if path.exists(SeachXl):        return True    else:        return Falsedef xlRunMacro(macroLink):    PathFile = macroLink[0]    xlMacro = macroLink[1]    isLinkReady = False    # Create the link with the open existing workbook    win32.pythoncom.CoInitialize()    xl = win32.Dispatch("Excel.Application")    try:        wb = win32.GetObject(PathFile)        isLinkReady = True    except:        NoteToAdd = 'Can not create the link with ' + PathFile        print(NoteToAdd)    if isLinkReady:        # If the link with the workbook exist, then run the Excel macro        try:            xl.Application.Run(xlMacro)        except:            NoteToAdd = 'Running Excel Macro ' + xlMacro + ' failed !!!'            print(NoteToAdd)    del xldef mainProgam(macroSettings):    FullMacroLink = []    PathFile = macroSettings[0] + macroSettings[1]    FullMacroLink.append(PathFile)    FullModuleSubrout = macroSettings[1] + '!' + macroSettings[2] + '.' + macroSettings[3]    FullMacroLink.append(FullModuleSubrout)    if isWorkbookOpen(macroSettings[0], macroSettings[1]) == False:        # If the workbook is not open, Open workbook first.        system(f'start excel.exe "{PathFile}"')        # Give some time to start up Excel        sleep(2)    xlRunMacro(FullMacroLink)def main():    macroSettings = []    # The settings below will execute the macro example    xlPath = r'C:\test\\'               # Change add your needs    macroSettings.append(xlPath)    workbookName = 'Example.xlsm'       # Change add your needs    macroSettings.append(workbookName)    xlModule = "Updates"                # Change add your needs    macroSettings.append(xlModule)    xlSubroutine = "UpdateCurrentTime"  # Change add your needs    macroSettings.append(xlSubroutine)    mainProgam(macroSettings)if __name__ == "__main__":    main()    exit()

VBA Excel Macro

Option ExplicitSub UpdateCurrentTime()    Dim sht As Worksheet    Set sht = ThisWorkbook.Sheets("Current-Time")    With sht        sht.Cells(2, 1).Value = Format(Now(), "hh:mm:ss")    End WithEnd Sub

You can use it also as a dynamic module too. Save the module above as RunExcelMacro.py in Your python project. After just use the following lines:

from RunExcelMacro import mainProgammainProgram(macroSettings)

It will do the job, succes ...

You need to reference the module name as well

Example here my vba code under Module1

Option ExplicitPublic Sub Example()    MsgBox "Hello 0m3r"End Sub

and here is my python

from win32com.client import Dispatchdef run_excel_macro():    try:        excel = Dispatch("Excel.Application")        excel.Visible = True        workbook = excel.Workbooks.Open(            r"D:\Documents\Book1.xlsm")        workbook.Application.Run("Module1.Example")        workbook.SaveAs(r"D:\Documents\Book5.xlsm")        excel.Quit()    except IOError:        print("Error")if __name__ == "__main__":    run_excel_macro()

• python
• excel
• vba
• win32com