Running an Excel Macro Via Python

Running an Excel macro via Python?

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

Code

import os, os.path
import win32com.client

if 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

Calling a macro to a different workbook python

If you plan on using this frequently I would rewrite the macro code in python+xlwings. This removes the issue of running things from other workbooks and will let you run it on multiple files at once by just adding looping logic:

macro.py

import os
import sys
import xlwings as xw
from os import listdir
from os.path import isfile, join

def python_macro(wb_path, app):
    #Optionally verify that files are all xls*, csv, or directory files

    wb = app.books.open(wb_path)
    sheet = wb.sheets.active
    
    #Rewrite macro in python+xlwings below
    print(sheet.range('A1').value)


with xw.App() as app:    
    for arg in sys.argv[1:]:
        path = join(os.getcwd(), arg)
            
        if os.path.isdir(path):
            #Optionally process further subdirectories
                
            files = [f for f in listdir(path) if isfile(join(path, f))]

            for file in files:
                python_macro(file, app)

        elif os.path.isfile(path):
            python_macro(path, app)

Save the file in the same directory as the report files and running it would look like this:

python macro.py reportthatneedswork.csv anotherreport.csv report-sub-dir

---

From what I could find, there isn't really a way to execute external workbook macros with xlwings. The closest thing I found was using win32com to extract and move over the vb script then you could use xlwing to open it an run the macro. However, this is particularly overkill for your scenario. It will be much easier to deal with if you just rewrite the macro in python.

Read VBA from workbook and then 'inject' it into another sheet.

Running an Excel macro via Python (using a Mac)?

Have a look at xlwings. It is a well thought out python package that allows you to control an excel application from python (and vice versa). It supports both Windows and Mac. On Mac it uses psutil and appscript behind the scenes to communicate with the excel application.

The xlwings documentation gives the following example for executing an excel VBA macro from python code:

Examples

This VBA function:

Function MySum(x, y)
    MySum = x + y
End Function

can be accessed like this:

>>> import xlwings as xw
>>> wb = xw.books.active
>>> my_sum = wb.macro('MySum')
>>> my_sum(1, 2)
3

Trying to open an excel using a python code

Simply make the Excel application visible. Without setting this Application.Visible property to True, the application runs in background and be sure not to call Quit. And of course with any API integration, be sure to wrap in try/except:

import win32com.client as wincl

excel_path = os.path.expanduser("Valuation.xlsm")

try:
    excel_app = wincl.DispatchEx("Excel.application")
    workbook = excel_app.Workbooks.Open(Filename = excel_path, ReadOnly =1)

    # RUN PROCESSING
    excel_app.Run("Macro1")

    # LAUNCH EXCEL VISIBLY TO SCREEN
    excel_app.Visible = True

except Exception as e:
    print(e)

finally:
    workbook = None; excel_app = None
    del workbook; del excel_app

---

Related Topics