Author Topic: Running VBA scripts from python  (Read 8480 times)

Dan

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
    • View Profile
Running VBA scripts from python
« on: March 07, 2011, 01:43:05 PM »
Hi All,

I am working on a project which links together a 3D modelling engine (3DS Max) and a structural analysis platform (Multiframe) via Python and VBA. The problem I have is connecting my python code with my VBA code (which operates through excel), therefore PyXll seems like it might be really useful to me.

I was wondering if it is possible to execute excel macros through python, win32com and PyXll? To connect to 3DS Max I am using a simple COM server and then using win32com's _flagasmethod command to execute scripts remotely. Here is an example of what I mean:

Code: [Select]
mxsCom = win32com.client.Dispatch('MAX.Application')
mxsCom._FlagAsMethod("command_from_python")
def GenComponent(FaceIndex,A1,B1,Ex1,A2,B2,Ex2,A3,B3,Ex3,A4,B4,Ex4):
    mxsCom.command_from_python("""
    FaceIndx = %i
    A1 = %f ; B1 = %f ; Ex1 = (%i * 10)
    A2 = %f ; B2 = %f ; Ex2 = (%i * 10)
    A3 = %f ; B3 = %f ; Ex3 = (%i * 10)
    A4 = %f ; B4 = %f ; Ex4 = (%i * 10)
    surf = $EvoSurface
    verts = polyop.getfaceverts surf FaceIndx
    ....

The benefit of using this system is that I can input variables from Python into 3DS Max scripts and execute them very easily.
What I am hoping to do with my Multiframe connection is similar. This time I have VBA code which is currently executed in Virtual Basic in Excel:

Code: [Select]
Sub Macro1()
' Macro1 Macro
Dim mfApp As New Multiframe.Application
Dim myFrame As New Multiframe.Frame
Dim myNode As Multiframe.Node
Dim myEl As Multiframe.Element

'Add nodes to frame
Set myNode = myFrame.Nodes.AddNode(0#, 0#, 0#)
Set myNode = myFrame.Nodes.AddNode(0#, 5#, 0#)
Set myNode = myFrame.Nodes.AddNode(6#, 5.5, 0#)
Set myNode = myFrame.Nodes.AddNode(12#, 5#, 0#)
Set myNode = myFrame.Nodes.AddNode(12#, 0#, 0#)

'Add Elements by joining nodes
Set myEl = myFrame.Elements.AddElement(1, 2)
Set myEl = myFrame.Elements.AddElement(2, 3)
Set myEl = myFrame.Elements.AddElement(3, 4)
Set myEl = myFrame.Elements.AddElement(4, 5)
End Sub

Would it be possible to use PyXll to help me execute this code from within Python using the _FlagasMethod procedure (or equivalent) to allow me to input and output data from the vba macro?

Any help or point in the right direction would be very much appreciated!

Many Thanks,
Dan

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 606
  • Karma: +14/-1
    • View Profile
Re: Running VBA scripts from python
« Reply #1 on: March 08, 2011, 10:07:28 AM »
Hi Dan,

yes, it is possible to execute Excel macros from Python. You can use the win32com module to get the Excel instance (either using win32com.client.Dispatch("Excel.Application") if you're outside Excel, or using pyxll.GetActiveObject if you're using PyXLL - see xl_app() in the automation examples).

Once you have the Excel application you can call any of it's methods, the same as you can in VBA or any other language.

You can use the COM method Application.Run to call a macro.

Why do you need to do this in an Excel macro though? You can do the same thing in Python (even without PyXLL), exactly as you have for 3DS Max.

You can call Python code directly from Excel as well, if you need to do that, using PyXLL. Have a look at the documentation for the xl_func and xl_macro decorators.

Regards,
Tony