IronPython - Run an Excel Macro

Question

I'm running TIBCO Spotfire v4. It has IronPython built in. Looking to run a Spotfire report that will export a .xls file (that part is done). Looking for a script to open an Excel file and run a macro to be able to format the file.

Here's some code I found and tried using. Not sure where the import items come from!

import os, os.path, win32com.client

def run_macro(fName, macName, path=os.getcwd()):
    """ 
    pre: fName is the name a valid Excel file with macro macName
    post: fName!macName is run, fName saved and closed
    """ 
    fName = os.path.join(path, fName) 
    xlApp = win32com.client.Dispatch("Excel.Application") 
    fTest = xlApp.Workbooks.Open(fName) 
    macName = fTest.Name + '!' + macName xlApp.Run(macName) 
    fTest.Close(1)
    xlApp.Quit() 
    xlApp = None

EDITOR - Code looks to be from Cannot iterate VBA macros from Python.

Answer 1

I had a similar problem (trying to run an Excel VBA Macro from ipy), and ended up getting it working.

Try this code:

# .net access
import clr
clr.AddReference("Microsoft.Office.Interop.Excel")

# opening the workbook
excel = Excel.ApplicationClass()   
excel.Visible = True
workbook = excel.Workbooks.Open(r"C:\your\file\here.xls")
# or open locally:
# workbook = ex.Workbooks.Open('here.xls')

# running the macro
excel.Run('YOUR-MACRO-NAME')

# closing down
excel.Quit()

The first section is using .NET , and the second part is actually executing the macro. Note that this is to run excel code from a excel file that is not open. If you want to run a macro from a file that is already open (which something I also had to do, so figured I may as well put in here), you would need to recognize it as an active object instead of opening it (see below). The code to run the macro would still be the same as above.

# recognizing an already opened window
from System.Runtime.InteropServices import Marshal
excel = Marshal.GetActiveObject("Excel.Application")

This link was very useful when I was looking for a solution:

http://www.ironpython.info/index.php?title=Interacting_with_Excel