3

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.

Community
  • 1
  • 1
user2540187
  • 31
  • 1
  • 2
  • Can you show us what you've tried? – Jon Lin Jul 01 '13 at 19:31
  • 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 – user2540187 Jul 02 '13 at 13:15
  • It looks like the existing answer covers your question, have you tried it and did it work for you? If so please consider marking it as the accepted answer or letting us know what's still not quite right. – RyanfaeScotland Jun 03 '15 at 17:30

1 Answers1

3

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

wonder
  • 312
  • 1
  • 3
  • 14
  • 1
    I had to comment out the DisplayAlerts line as it was throwing an exception (known bug apparently) and also use excel.Quit() instead of excel.Exit() but other than that this looks to be a correct answer +1. – RyanfaeScotland Jun 03 '15 at 17:06
  • @wonder I tried your code, if I run the Macro directly from Excel VBA Editor, it does the tasks it's supposed to do. But from IronPython I get the error that such a script does not exist or it's not in the working directory ( which I don't know if it refers to Excel working directory or IronPython ?) – FabioSpaghetti Sep 02 '18 at 19:26