Dynamic Diagramming in Excel: Calling Excel commands from Rhino:

Replies to This Discussion

Permalink Reply by Damien Alomar on February 27, 2010 at 9:15pm

Do you need to do this yourself, or do you just need a solid way to read data from Excel? The reason I ask is that Giulio, Luis, and I are redoing a lot of the interop/connection stuff that's come up over the last year or so, so reliable ways to connect to Excel will be available in a little while. So is it just that you need the Excel data, or do you need it in such a way that it needs to be customized?

Permalink Reply by Ben Fortunato on February 27, 2010 at 10:42pm

I would like it to be customizable. . . . Their is an example on the rhino wiki of a script that calls out excel commands such as creating workbooks etc. Is it possible to do the same in grasshopper? It seems like it should work except that I was having the problems above. I can get around not using the set command but I need to be able to pass arguments to the excel methods, which are constants declared within excel, such as xlthick for a thick line. I think maybe the problems is that I can't import the excel name space? and thus these constants are not importing?

The other option would be figuring out a way to grab data from rhino through excel's vb. But I'm not sure how to grab grasshopper and then how to grab the input stream
into the specific vb module you are working on. . . but this just sounds complicated.

Permalink Reply by Luis Fraguada on February 28, 2010 at 4:27am

hmmm, to me it sounds like the first step would be to set up the diagram in Excel, and feed a certain range of cells some data from GH and watch it update...Maybe try that as a proof of concept. To instantiate a diagram from a GH component, there would need to be specific code for it...in Rhinoscript, I would need to look it up on msdn to see what the code is...

Permalink Reply by Ben Fortunato on February 28, 2010 at 8:15am

I posted some pictures of what I wanted to do would look like. The only thing tripping me up is how to

1) pass a list of data from rhino to excel which could then be used in an excel vb script

2) or how to call excel vb commands from within rhino

The idea is that data from grasshopper could produce a very basic diagram or certain conditions, in this case floor plates and stairwell locations. The user could then "draw" using the formatting tools. In this case the dark colors would represent a change in the fenestration pattern Code in excel would search for the formatting (easy enough to do through Excel Vb) and output the resulting data into another workbook.

Basically you would be using excel as a CAD tool. The advantage of this is that it gives you a flexibility that pure code can't. Which I think is very exciting, because code can't deal with exceptions very well. For example, another possible use I could think of would be the manipulation of a brick wall. Since excel has multiple worksheets, you could LAYER code for one object. One worksheet could give instructions on brick rotation the other on a cull pattern. . . . The advantage of excel is that you can vary these patterns based on the location of your program for example, so that the density would decrease at a certain point. With the vb script, you could also get quite sophisticated with the patterning. In essence each cell is a pixel.

image by danielle aubert "excel artist"

The difference with excel, which makes it a very POWERFULL tool in my opinion is that you are not trapped into what your code outputs, which often is incredibly difficult to have respond to various condition, program, sunlight etc. With excel you can use code to generate a basic pattern, but then modify it. You can also layer code, just like you do in photoshop to produce different effects on the same geometry. You could even execute different code in different areas. . . . Anyway, I thought that it was potentially a game changer. Let me know what you think. And if you have any pointer on , how to start running excel code from rhino or importing rhino data to excel please share so I can start playing with these ideas.

Permalink Reply by Damien Alomar on February 28, 2010 at 8:49am

My suggestion will be to use the Macro Recorder on this one. I've used it in the past to get a quick example of you to do some "obscure" thing, and its worked well. The only reall downfall is that it creates a lot of "useless" code, but its easy to work around this.

So basically, start with a blank sheet, and enable the macro recorder. Then go ahead and start doing some of the things that you'd like to do. I would not recommend doing everything all in one shot. Just do one aspect here, stop the recorder, start it up again, do another thing, so that you have small managable chunks.

Essentially what Excel will spit out after all of this is the step-by-step instructions to do everything, but expressed directly in code. If you want to write all this information, then your code will be very close to the recorded version. Reading might take a switch in a few methods (some only set information while others only get), but again, the code should translate without too many issues.

As to reading/writing from excel in general, you can either fire up a new instance of excel using very similar syntax to the RS examples, or you can connect to a running instance of excel (take a look at my post here if you haven't already). I would recommend trying to connect to the running excel instance first, just make sure to have it started before running the component. After that, within the scripting component you should be able to get away with accessing things just as you would with the excel code, however all of the excel types that are being used (workbook, worksheet, range, etc) would all just be referenced as an Object instead of its regular excel type.

Permalink Reply by Ben Fortunato on February 28, 2010 at 9:29am

I got the excel script down, (or at least can figure it out) and I have looked at your post as well as the one on LAN's website. (Thanks by the way for putting this up, very helpfull!) The problem is that one can NOT just access things as one would with excel code. There are some syntax issues as I mentioned in by first post.

More specifically, excel constants which I'm trying to pass to the excel borderaround method (for example) are being read by the Grasshopper VB component as variables. I believe it has something to do with importing a namespace, or some dll file so that grasshopper understands the xlthick is an excel constant not a user defined variable. (This is all just speculation, I've been trying to get my head around all this the last month, but I have a degree in arch not comp science! lol)

If you could look at my ghx file that would be great. . . . Some more specific advice would be greatly appreciated as I'm quite new to coding, and these syntax issue can be a real snag. The first VB component tries to import a series into excel, the second tries to do some formatting in excel from Rhino.

I think that this would be something of interest to the larger grasshopper community as well, letting people read and write values to excel and to have access to the math functions and spreadsheet capabilities in Excel as well as the potentials mentioned in my post above about layering and the ability for the spreadsheet to act as a matrix which can allow for more nuanced responses to various conditions, in the end making code a bit "smarter".

Permalink Reply by Ben Fortunato on February 28, 2010 at 9:31am

The file is attached here:

Attachments:

• excel link upload.ghx, 74 KB

Permalink Reply by Damien Alomar on February 28, 2010 at 10:06am

There are always more than one way to do anything, and this goes double for programming. However, just because there's one way to do something doesn't mean that all of those ways will work. Visually, I can't really see any reason why your code wouldn't work (except possibly the way your setting the range, but I'm not sure). Since the connection to excel is just going through COM as opposed to referencing the proper libraries, or working directly in Excel, there's likely some methods that won't work as expected (sorry for thinking that they would...I haven't run into any, but its obvious that you're doing things differently that I have).

My best advice would be that if you keep away from "complex" variable types (anything other than a string or a number would be considered "complex" here), then you'll have better chance for success. Personally, I always write my cells one by one using the Cells(intRow, intColumn).Value method. It may require a few more lines of code, but its always worked for me. I suspect that trying to pass the array through COM is not working and throwing up an error.

As to using excel constants, you just can't do that unless you have a reference to the excel library. Since you don't here, then only way to do this would be to use the actual enumeration value that the constant represents. There's a list of these somewhere, so I'll try and see if I can track them down.

Permalink Reply by Ben Fortunato on February 28, 2010 at 12:08pm

I will give the cell by cell method a try. Easy enough to do via a loop. I guess what I can do is write information into excel, on a separate worksheet and then use Excel code to grab whatever info I need from that spreadsheet. That way the excel code stays in excel.

Not sure why I didn't think about that. It would be nice to have the grasshopper vbscript run the excel function so that the "diagram" rebuilds when changing variables. But maybe this is too complicated? At this point I just want to forge ahead and if I have to re-run the script in Grasshopper and excel manually that's fine.