Starting Excel VBA macro from GH using VB Script Component

Hello everyone,

I'm currently working on a coupling between GH and Excel. For this i make use of the "ExcelWrite" component to write data from GH to an import sheet in an macro-enabled Excel worksheet. I would like to use the "VB Script" component to automatically export the data to the excel sheet using the "ExcelWrite" component, but more important, i would like to start several macro's in Excel itself. Since i'm not that familiar with the "VB Script" component and the VB commands i would like to get some help in putting this script together. Here is what i would like to happen when the script runs:

The "ExcelWrite" component is started, data is exported to the excel worksheet;
The Excel-file is opened and the macro's are started;
After completion of the macro's the file is saved and closed;
The "ExcelRead" component is started, data is imported from the excel worksheet back into GH.

I really would like to get some help with this, writing VBA in Excel was not that hard but right now i don't know how to start at all. Any suggestions are more than welcome!

Best regards,

Patrick Marinus

Eindhoven University of Technology

The Netherlands

Attachments:

Grasshopperplot.png, 1.1 MB

Replies to This Discussion

Permalink Reply by Stefan on May 14, 2013 at 12:45am

I don't know whether what you plan can be done. But if it's possible, I suspect it will be quite slow. For every computation, Excel will be launched, and that takes very long.

If you are willing to use VB, you could possibly migrate the code from the Excel macros into a scripting component in GH. That will be some work in the beginning, but it could save time in the long run.

From your screenshot I gather you are working on something related to reinforced concrete. I've been doing things along this line, though not exactly what you are working on. If you are interested (and I find the time ;) ) I could try if I can be of some help...

Best regards
Stefan

Permalink Reply by David Rutten on May 14, 2013 at 1:42am

Another approach would be to leave Excel running and use its API to pump your data back and forth directly. I've never actually used Excel in my life though so I have no idea whether it's possible to write an Excel-script that can handle this.

David Rutten

david@mcneel.com

Poprad, Slovakia

Permalink Reply by Patrick Marinus on May 14, 2013 at 2:17am

Hello David,

The problem isn't so much the transfer of data between GH and Excel. Ideally i would like to let Excel run in the background and use GH as my main interface where i can change the input and after calculation with Excel see the output. Therefor i need to open the Excel worksheet using GH and also start the macro's in the sheet using GH. Thanks for you reply. If you have any other ideas please let me know!

Regards,

Patrick

Permalink Reply by Patrick Marinus on May 14, 2013 at 1:43am

Hello Stefan,

I'm indeed working on a model related to reinforced concrete.

I'm not looking for a continious update of the Excel sheet, i would like to decide when the export, calculation and import is started by clicking on a button that starts the VB component.

I would really appreciate your help or any tips you might have!

You are probably right about migrating the code from Excel to GH. For now i would like to start with my first idea since i only have several weeks left for this research project which involves the GH model.

Thanks and best regards,

Patrick

Permalink Reply by Stefan on May 14, 2013 at 1:59am

One thing you could do is:

Use Events in Excel, e.g. Workbook.Activate Event (see http://msdn.microsoft.com/en-us/library/office/ff823078.aspx ). This get called every time you activate the workbook. In this sub, you can place all calls to macros you wish to execute.

This will give you a semi-automated solution. Excel is not started by grasshopper, but you don't have to load the file every time you change your data.

I attach some notes I took for myself on Events (in German unfortunaltey)

Attachments:

screenshot.1.png, 47 KB

Permalink Reply by Patrick Marinus on May 14, 2013 at 3:57am

Thanks for your reply and suggestion Stefan, your notes are easy to understand. In order to make use of this Event in Excel i first would like to be able to open the worksheet using GH. Maybe it's better to focus on this action/connection first. I will try to find some books in the University Library on Visual Basics and Macro's in Excel.

RSS