Practical Dynamo - Excel Linking

At some time or another, I think we have all grumbled about Revit's lack of interest in communicating with Excel.  The minions of Bass implored us "LINK TO MS/ACCESS!!!"

yeah right....

In response many of us have expended resources on bi-directional excel links using the API.  Sometimes purchased, sometimes internally developed, but there was always a substantial cost involved.

Then along comes DYNAMO.  There is not shortage of pretty pictures of its iterative design capabilities but i'm more practically minded. I realized in an instant that this was the tool that could give me an on the fly, bi-directional excel link... if only i could figure it out.

I've taken some babysteps and i thought i would share.  There is a hardcore group of guys using it, but i am doing my part to spread the word to the mass market.

Here's what i did, starting with a field of orbs.

I've given all the orbs instance marks of AAA to KKK.  My goal is to export the data to excel, add a column in excel with new data, and finally port the new data it back into the "Comments" field.

Getting the Data Out

First thing was to make a new DYNAMO file that I called "Excel Out.dyn"

I'll work from Left to right through the steps

Family Types - Identifies the family type within the Revit model you would like to work with.
All Elements of Family Type - Tells DYNAMO to select them.

Element.GetParameterValueByName - Returns a list of the values for a given parameter within the selected families.
String - Literally a string of characters.  In this case, the name of the parameter i want DYNAMO to get for me.  This kind of work is case-sensitive.

Watch - These are the boxes that will let us see what DYNAMO is doing.  The box on the left shows me the list of values in the "Mark" parameter.  Its not in alphabetical order and I don't care.  On the right is the list of values for the "Comments" parameter.  Its empty, which is accurate.

List.Create - Now i am compiling the data i've culled. index0 refers to the list of values from the "Mark" parameter, index1 refers to the list of values from the "Comments" parameter.

List.Transpose - The previous lists were formatted as columns, however when Dynamo and Excel talk, the data should be formatted by row.  This button reorganizes the data.  Now i have a list for each family instance where item [0] = the "Mark" parameter and item[1] = the "Comments" parameter.

Excel.Write - This is the node which will open an excel file and write to it, but it needs some instructions
File Path - Tells DYNAMO where to find the .XLSX file
sheetName - Must indicate which sheet within the workbook is to be referenced.  I use a text string to indicate
startRow & startCol - Tells DYNAMO where to start reading the spreadsheet
data - Is looking for the lists we created earlier

I hit the RUN button in the bottom left corner.  It thinks for a second then i open my excel file and i have this:

Perfect.  My exported values are in the first column, the second column is blank as anticipated.  For now i am still ignoring the lack of alphabetical order.

Getting the Data In

Using the CONCATENATE function in excel, i went ahead and added the data in column b that i will pump back into my families.

For this part, I made a new DYNAMO file that I called "Excel In.dyn"

Again I'll work from left to right:

File Path - Gets the Excel file
String - text string to indicate which worksheet
Excel.Read - The node which tells DYNAMO to open the file and read it
Watch - Showing me what it comes up with.  Notice how it has made me a series of lists showing what was in each row.

List.Transpose - This is what turns the list of data by rows into a list of data by columns
List.GetItemAtIndex - This is an instruction to pull a particular list out of the list of lists.
Number - "1" pointed at the "index" slot tells it that i want list [1]
Watch - You can see that it has isolated the data i want

Element.SetParameterByName - This is where the magic happens.  This is the node which will write to data to the parameters in your families.  Of course it needs some information.
In the element slot we get:
Family Types - Indicates the families available in the model.
All Elements of Family Type - Tells it to select the indicated family.
In the parameterName slot i use a String node to indicate the parameter i want to write, "Comments".
The value slot links back to me previously isolated data.

I hit the RUN button again and success!

There is still some ways to go with this but i am really happy that i have made some headway.  More help is available here from guys who are lightyears ahead of me:

http://dynamobim.org/