Run excel macro from Dynamo

Hi,

 

I would like to find out if there is any way to run macro from Excel. I want to open excel run selected module from excel macro once this is done take data from standard excel sheet into Revit and then close excel. I have connected so calcs to external software and I can run it from VBA Macro in excel.

Hope to get reply,

Michal

Michal,

Yes, you can run Excel VBA Macros using the COM Interop so effectively you could do that from Dynamo or any other software (Grasshopper). As a matter of fact, If I am not mistaken a Bumblebee plug-in for Grasshopper allows you to do just that already, although I am not 100% sure that functionality still works because it has been a while since I have used it. Point is that it’s possible. Look into Excel COM Interoperability. You can take clues on how to set up your Excel connection from Dynamo by peeking under the hood of Bumblebee for Dynamo plug-in that i have written.

Good luck!

This sounds optimistic… I will look and see if I can get my head around this…

Hi Michal,

I would like to run an Excel macro from Dynamo, which operates in my Excel file. I wasn’t able to do that so I wonder if you’ve found any solution to make that link between Dynamo and Excel macros, did you?

Thanks

Gauthier

Why not map your excel macro with python. I haven’t tried but you can try and see if it works.

http://pythonexcels.com/mapping-excel-vb-macros-to-python/

This code is to run excel macro from Python https://mail.python.org/pipermail/python-win32/2003-June/001084.html

Good Luck!

That seems to be a great idea ! Even if I’m not familiar with Python, I definitely need starting to learn it.

For sure I’m going to get results with your advice and links,

Thanks a lot!

I still did not manage to get it working…so any successful story, news are appreciated :slight_smile:

Has anyone found the solution for this?

try this

import clr
import time
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, 
PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
from System.Runtime.InteropServices import Marshal
#time.sleep(3)

dirfichier = r'C:\TPS\Classeur1.xlsm'
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False

wb = ex.Workbooks.Open(dirfichier)
ws = wb.Worksheets[1]
# some code
#
#  time.sleep(3)
# r un macro named 'Dynamo' in excel
ex.Application.Run("Dynamo")
wb.SaveAs(dirfichier)

ex.Workbooks.Close()
ex.Quit()
1 Like