Last week I wrote about using note block schedules to create specification sheets and I explained how you can use addins such as BIMLink, custom macros or C# addins or Dynamo to get the data out to Excel so that it can be edited by an engineer and then re-imported to Revit from Excel.
Problem is, you don’t have a BIMLink licence, you don’t know how to code in C#, the most accessible method for you to achieve this workflow is Dynamo but you don’t even know where to start; never fear! In this post I explain how to create a bi-directional link between Revit and Excel using Dynamo using the as the note blocks from my previous post as an example.
I’ll be using Dynamo 0.80 which can be found on the Dynamo BIM download page.
Exporting from Revit to Excel
The export to Excel process is pretty simple, you just need to think about the logical steps that you need to take. You want to select the family you wish to export data from, select the parameters to export, organise them in a list and then write it to the Excel file itself.
To step through the process, first select the family type and push that into an All Elements of Family Type node.
From there we start our first step in taking care of the family unique identifier by using the Element.UniqueId node, this will export the family’s GUID. Simply link the All Elements of Family Type node to this one. You need to export each individual family’s unique identifier as well, if you don’t, as the BIM Troublemaker discovered you may end up importing the wrong data to the wrong family.
Next, you need to select each of the parameters you want to export, for this you need to use the Element.GetParameterValueByName node. Link the All Elements of Family Type node through to this node and you will also need to add a String node which is where you input the name of the parameter. Because I’m using the annotation family from my note block post, the parameters that I will be exporting are NUMBER, NOTE, CATEGORY and REGION. Note that the value that you enter into the string is case sensitive and that you need to repeat this for each parameter you are wanting to export.
Now you need to push the output of each of our Element.GetParameterValueByName nodes through to the List.Create node. This will actually create a list that will run the parameters across the page with each parameter being spread across a row instead of being arranged in columns. If you exported the data to Excel at this point, you would end up with something that looks like this:
However this isn’t how we want to work, we like our data arranged in columns. To do this, use the List.Transpose node which swaps the rows and columns in the created list.
To finish off we need to use the Excel.WriteToFile node in which we pass through a File Path node, a String which names the Excel sheet and a few Numbers which gives the starting row and column of our excel sheet.
The result is a nice, easy to read export of all the data relevant to our note block schedules ready to be modified by an engineer.
At this point you can use the Sort function in Excel to sort your Excel file into a usable list. In this instance I have chosen to sort by Column D and then Column B so that I am sorting first by category and then by note number.
Importing back into Revit from Excel
Once the engineer has finished modifying the Excel sheet, we’re ready to bring the data back into Revit from Excel. This process is slightly more cumbersome but overall not a whole lot more difficult than the export process.
First we need to pick up our Excel file, start with the File Path node that we used before, before we go to the Excel.ReadFromFile node though we need to pass the file path through the File.FromPath node otherwise it will not work. We also need to add a String so we can tell Dynamo which sheet to read. Next, pass the data back through the List.Transpose node to get the data back into a format that Dynamo and Revit are happy to work with.
Remember earlier I mentioned during the export process that to make sure we’re writing the correct information to the correct family we need to index our list and the families by their GUID? Thanks to the BIM Troublemaker, we know how to select each element from our Excel file based on the GUID. First, start with a List.GetItemAtIndex node and connect the List.Transpose node to it. Next, add a Code Block node and enter the text 0; this passes through the number 0 as our row index. Finally, add a Code Block node and insert the code ElementSelector.ByUniqueId(id, true); this is case sensitive, so make sure that you enter it correctly or it will not work!
Finally, we need to push the data through to each parameter for each individual family. To do this, use an Element.SetParameterByName node. We need to feed the ElementSelector Code Block that we created into our node, along with a String node that matches our parameter name, remember this value is case sensitive and finally we need to link up our value via a List.GetItemAtIndex node.
The List.GetItemAtIndex node needs to be told which row to use to pull it’s data from, this is as simple as adding a Number node. Don’t forget though that the first row is at index 0, which is where we are pulling our GUID from, so we want to pull our first series of parameters from the row which is at index 1.
In this instance our index 1 is our number list which you may not need to import this so you could skip this step, but if you do need to populate the number data and you try to feed the parameter from the List.GetItemAtIndex node directly to the Element.SetParameterByName node Revit will throw back an error stating that “The parameter storage type is not a number”. Originally I tried to overcome this by using the node String from Object but this gives a number to 3 decimal places and we don’t want to see this on our specification sheet. The solution in this instance was to first use the Math.RoundDownToPrecision node from the Clockwork package which you can download from within Dynamo itself or from dynamopackages.com. We need to pass a number to the Math.RoundDownToPrecision node to indicate the precision we want, which in this case we want a precision of 1.
The number conversion is the only oddball of the parameters that we’re working with, the rest of the parameters simlpy feed the List.GetItemAtIndex node directly to the Element.SetParameterByName node. Repeat for each parameter that you want to populate.
And to finish up, our results:
Great post, been trying to figure out a way to do this for a while! Also loved your note block schedules!
Keep up the good work
Hi Ryan, I’m adapting your route out to excel for pile loads from my model. The route out works perfect but keep running into errors with the return journey. Can I send you some screen shots for some suggestions?
Sure Lee, you can either put them in these replies, or send me an email to ryan at mistermep.com
I’ve also added a contact me page to the site to make getting in contact easier! You can find the link to the contact page in the sidebar menu on the left.
Just stumble on your post…Been doing same thing for General notes but had problems of importing it from excel…You saved my day, tnx…Keep the good work
Thanks for the post.
I have set a challenge for myself to use Dynamo to generate content for Revit’s graphic column scheduled. I have had success with BIMLink, but I feel that Dynamo is what the next level Revit operator should cover.
Thanks for this. On the ‘Importing back into Revit from Excel’ part , I am not finding an ‘Excel.ReadFromFile Node. Is there another node I should use in its place?
Hey Eoghan, this graph was made quite a while ago so the nodes have been updated since this post.
What you’re after is Excel.Readfromfile
https://dictionary.dynamobim.com/#/Office/Excel/Action/ReadFromFile