How to run Excel VBA / Macro from Python

Question

I am trying to run a VBA Macro in an xlsm workbook using python 3.7 in Spyder. This workbook has two worksheets.

The code that I have currently runs and saves the new file with no problems, however it is not triggering the VBA like it should.

I know this macro works because if I manually click the button in Excel, it works just fine.

Could someone assist with this? I checked the Macro Settings under the Trust Center and all macros are enabled so I do not think it is a permissions issue, however I am not an admin on this pc.

The code is below:

import os
import win32com.client

xl = win32com.client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open("Z:\FolderName\FolderName2\FileName.xlsm")
xl.Application.Run("MacroName")
wb.SaveAs("Z:\FolderName\FolderName2\FileName1.xlsm")
wb.Close()
xl.Quit()

Answer 1

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

Answer 2

You need to reference the module name as well

---

Example here my vba code under Module1

Option Explicit
Public Sub Example()
    MsgBox "Hello 0m3r"
End Sub

---

and here is my python

---

from win32com.client import Dispatch


def 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()

Answer 3

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.client

xl =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

Answer 4

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, path
import win32com.client as win32
from time import sleep

def isWorkbookOpen(xlPath, xlFileName):
    SeachXl = xlPath + "~$" + xlFileName
    if path.exists(SeachXl):
        return True
    else:
        return False

def 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 xl

def 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 Explicit

Sub UpdateCurrentTime()
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Sheets("Current-Time")
    With sht
        sht.Cells(2, 1).Value = Format(Now(), "hh:mm:ss")
    End With
End 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 mainProgam
mainProgram(macroSettings)

It will do the job, succes ...