excel

Fixing SP.Writer Run Time Error 70

If you’re like me, you haven’t used SP.Writer in a while but then if you’re also like me, you will at some point need to use it to generate a project customised shared parameters file. The first thing you do is pull out your trusty SP.Writer only to find that when you create a new parameter you get a run-time error 70.

If you hit the debug button, you will be taken to the mod_GUID module and the 4th line will be highlighted as the problem child.

The function used to create the GUID in SP.Writer was patched in the July 2017 security patches for Microsoft Office so if you’re up to date with your security patches, this is working as intended. That doesn’t help us in the slightest with creating shared parameters though, so what’s the solution?

First if you haven’t already done so, you’ll need to enable the developer toolbar in Excel which you can do by following the instructions over at the Microsoft website.

Once enabled, select Visual Basic from the developer tab on the ribbon. Alternatively you can use the ALT+F11 shortcut on your keyboard.

On the left hand side of your screen, you will see a window titled Project – VBA Project, scroll down until you find Modules. Right click on modules and from the contextual menu select Insert.. -> Module.

Copy and paste the following code into the new module.

Private Type GUID_TYPE
Data1 As Long
Data2 As Integer
Data3 As Integer
Data4(7) As Byte
End Type

Private Declare PtrSafe Function CoCreateGuid Lib "ole32.dll" (guid As GUID_TYPE) As LongPtr
Private Declare PtrSafe Function StringFromGUID2 Lib "ole32.dll" (guid As GUID_TYPE, ByVal lpsGUID As LongPtr, ByVal cbMax As Long) As LongPtr

Function CreateGuidString()
Dim guid As GUID_TYPE
Dim sGUID As String
Dim retValue As LongPtr
Const guidLength As Long = 39 'registry GUID format with null terminator {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
retValue = CoCreateGuid(guid)
If retValue = 0 Then
sGUID = String$(guidLength, vbNullChar)
retValue = StringFromGUID2(guid, StrPtr(sGUID), guidLength)
If retValue = guidLength Then
CreateGuidString = sGUID
End If
End If
End Function

You can also rename the module, I renamed mine to mod_CoCreateGUID as this is will be method we’re using to generate the GUID. If you can’t see your properties window, press the F4 button.

 

Next, open the module mod_GUID and replace the existing code with the code below

Sub GetGUID()
Dim sGUID As String
sGUID = CreateGuidString()
wsData.Cells(LastRow + 1, 2) = "PARAM"
wsData.Cells(LastRow, 3) = Replace(Replace(sGUID, "{", ""), "}", "")

End Sub

 

The final step is to make a small change to the UserParameter form. In the VBA project, find the UserParameter form and right click and select View Code from the contextual menu.

Search for the code

'generate the GUID
GUID

and replace with the code

'generate the GUID
GetGUID

and that’s it! You’re done! You can now start creating shared parameters again. The GUID looks a little longer but it’s actually just the difference in length between upper and lower case with the font used.

Practical Dynamo – Moving Views on Sheets

For those of you that read through my previous post last week on creating sheets using Dynamo, you might have come to the end of the post only to realise that the views haven’t placed where you want them to be on the sheets.

For example, my sheet with the automatically placed view now looks like this

The first method I’m going to use nodes from both the Rhythm and Lunchbox packages which you can download from your package manager. Simply install the latest version.

 

The Rhythm package has some super useful tools for a whole range of different actions in Revit, but today we’re going to focus on the nodes that can help us manipulate the location of our views on the sheet.

To get started, we use the Sheet.GetViewportsAndViews node, we want to feed the sheets from our previous steps into this node and the node will give you the viewports, views and schedules as separate outputs. For this exercise, we’re only interested in the viewports. As always, while you’re reading through just click on the images to see them full size.

Next you need to use the Viewport.LocationData node from Rhythm. The outputs from this node are

bBox which returns the minimum (bottom left) and maximum points (top right) of the viewport bounding box.
boxCenter which returns the centre point of the viewport bounding box
boxOutline which returns the start and end points of each side of the viewport bounding box

For this example, we’re going to use the boxCenter option because we’re going to get tricky with it a bit later on. For those earlier that were wondering what the Use Levels option actually on the nodes, as you can see in my animation it changes the level of the list that we’re working with. Without the Use Levels option you would need to either use GetItemAtIndex or List.Deconstruct to get the data that you want to manipulate.

Next use the Points.DeconstructPoint node from the Lunchbox package, this will deconstruct your point into it’s individual X, Y & Z coordinates.

Now this is where we get too smart for our own good. I want my view to be placed in the middle of the available space on my titleblock. For my particular titleblock I know that the centre point is located at 378, 297 (yours may be different) and we already have the centre of the viewport from our Rhythm node.

To find how far we need to move the viewport, we need to subtract the view X centre value from the sheet X centre and the view Y centre value from the sheet Y centre. The code block is simply values I’ve chosen, you could think of them much like a parameter in a family.

The next step is to move the views. The vector gives the distance in X & Y coordinates that the view needs to be moved, the Vector.ByCoordinates and Element.MoveByVector nodes are both standard nodes within Dynamo.

And finally, the whole thing is tied together by pushing the viewport elements into the Element.MoveByVector node via a List.GetItemAtIndex, from which we’re taking the list elements at index 2.

Now sometimes when I run this script, I’ll see the following “Attempt to modify the model out side of transaction” error.

There is a simple solution to this. Just save your changes in Dynamo, close Dynamo and then re-open. Simply run the script again and everything will work!

 

An overview of our extension to the original graph from last week, I’ve highlighted the nodes from custom packages to make things a little easier as well, Captain BIMCAD actually called me out on last week’s example for not grouping my nodes!

Practical Dynamo – Generate Sheets from Excel

I was discussing Dynamo workflows with good old Captain BIMCAD the other night and we got to the topic of project setup.

Personally I don’t use Dynamo in my everyday project setup workflow, I use Ideate BIMLink, Omnia Scope Box Synchroniser and Sheet Duplicator but if you don’t have access to this software; especially BIMLink as it’s a bit pricey, Dynamo is definitely a viable option. Here’s how to get it done.

First we need to create a list of sheets in Excel with Name and Number information. Starting with a blank workbook in Excel, create a list with sheet numbers in column A and sheet names in column B.

From here we need to generate new sheets with this Excel data. Don’t forget the File.FromPath node, you can not feed the File Path node directly into the Excel.ReadFromFile node. Note that the name of the sheet in the Excel workbook is case sensitive. You can click on the image to view in full size


 

The next step is to remove the headers from our Excel file. They’re useful to us as it makes the Excel file more readable, however they need to be removed when used in Dynamo.

To achieve this we’re doing to use 2 nodes, List.FirstItem and List.RestOfItems.

 

Next we need to transpose our list so that we can feed in our sheet details into the sheet creation node. You can see once we run the list through the List.Transpose node that we now have a list of sheet numbers and a list of sheet names which sets us up for our next step.

Most of the magic happens at the next node which is the Sheet.ByNameNumberTitleBlockAndView node.

For the node to work, we need to input the sheet name, sheet number, the titleblock family which you can see how we achieve this in the next screenshot.

While you’ve been reading, I’ve taken it upon myself to generate some views in our model and add them to our original Excel file.

We can copy what we’ve already created in Dynamo for the sheet names and numbers and we simply take index 2 from the list, giving us the view names. Note that these will be case sensitive.

The next step is to actually find those views in the model to drop onto the sheets. We do this by creating a list of all the views within the model. Take the Categories node and select Views from the drop down, feed this into the All Elements of Category node and then finally feed this into an Element.GetParameterValueByName node. For the parameter name, we want to get the value for the View Name parameter.

From here we need to search the list of view names in Excel with the list of view names in the model. To do this, use an IndexOf node.

When you run this though, you’ll end up with a result of -1 instead of a list of indices. To fix this, change the level of list in the node. To do this, click on the right arrow on the element input of the node, select Use Levels and select @L1. Run the graph again and you’ll see the list of indices.

But what happens if you have a model where you don’t have the views setup yet? In our example we don’t have a view for the cover sheet or site plan yet which is why the view name is represented as null. You can see that the null view names give a -1 index result. If we feed this data into the Sheet.ByNameNumberTitleBlockAndView node as it is, it won’t create the sheets with the null views.

You can still use the same node, but there is a trick to it.

First, grab the Manage.ReplaceNulls node. Feed the list for views into the data section.

Next, create an empty drafting view, I’m just going to leave mine as the default Drafting 1. Feed the ReplaceWith input of the Manage.RemoveNulls node with the string Drafting 1.

Now when we search our views in the model, we’ll have the correct indices returned.

But hold on there a minute! We can’t drop drafting views on multiple sheets, how is this even going to work? To be honest, I’m not quite sure why but if you feed an empty drafting view into the Sheet.ByNameNumberTitleBlockAndView node it will generate an empty sheet. Whatever the reason, that’s a win for us!

Simply feed Manage.ReplaceNulls into the Sheet.ByNameNumberTitleBlockAndView node and we’re done!

 

If you’ve had automatic run selected, you’ll have a nice set of shiny new sheets created, otherwise simply click run and watch the magic happen.

Blink and you’ll miss it!

The end result. Click the image for the full resolution version.

Using Dynamo to Generate Mark Parameters from Information in Families

I recently had a question as a follow up to my bi-direction Excel using Dynamo post

Hi Ryan,

I’m trying to use the same concepts to export all structural columns. The node you used (family types) only exports 1 type at the time.

Do you know if there’s a node that lets me export all types of the column family I use in the project?

The node that you want to use to do this is All Elements of Category which you need to feed with your selection from the Categories node.

I thought I would share an example of how I used this node to generate individual type marks for all the columns on the project based off the family name and the level that the column is located on.

Of course generating the mark parameter from the family name means that you need a fairly solid and consistent naming convention in place. You could also use other type parameters within the family, this is just the method that I chose as an example.

Before we get started, I have used the Revit 2015 Sample Structure Project.RVT file and renamed the family types like so

 

The overview of this particular example of the Dynamo Script looks something like this, so let’s step through what it is doing.

2015-07-13_14-07-01

Getting started, we use our Categories node which we use to select our Structural Columns category. As mentioned earlier we feed that into the All Elements of Category node.

2015-07-13_11-12-25

Stepping through the top row, our elements feed into the Element.GetParameterValueByName node, and we’re picking up the Base Level parameter.

2015-07-13_13-49-43

2015-07-13_13-51-31

We then pass through to the Level.Name parameter which gives us the level name as you see it in the project browser as a string, which in this case is “01 – Entry Level”. Using the String.Split node we split the string in half using a Code Block node and entering ” “; into the code block to denote the space.

Think of this as the same as the Text to Columns command in Excel.

This gives us a list that splits the level name into

“01” “-” “Entry” “Level”

From here, run the data through the List.Transpose node and then finally into the List.GetItemAtIndex node, here we need the row at line 0 so we use a Number node to define the index.

You can alternatively use a Code Block node and enter “0”; as the code which works the same as the Number node.

On the bottom row we are taking our family type name and pulling out the type itself from the string that is returned. Even though when scheduling the Type parameter returns just the type, the string that is actually returned from Dynamo is in the format of Family Type: CRC 450, Family Name: STR_CONCRETE_ROUND_COLUMN.

So following a similar sequence to the top row, we split the string down to what we want which is “CRC 450”, to do this we need to split the string twice; first by the colon ( : ) and then by the comma ( , ). As you can see in the screenshot we need to transpose the list and pull the data at each row twice to get what we’re after.

2015-07-13_14-20-45

At the lower right corner there is a number generator. It is simply stepping from 1 to 200 in increments of one. Make sure to run the Number.Sequence node through the List.Transpose otherwise you will cause Dynamo to lock up while generating incredibly long number strings if you feed the sequence directly into the mark parameter.

 

Finally, we use a Code.Block node to concatenate the data we have pulled into a single string. The code block is simply a+b+c+d; which gives as four variables of the same name that we can feed the rest of our data into.

I have then created another list which consists of the Element ID and the string, we then finish up by selecting our string and populating the mark parameter as per my bi-directional Excel post.

Of course there are other options where you can generate the mark parameter from other type parameters in the family, I just chose to use the type name as the example this time around as I could show how you could break down a long string and the data you need from it.

Taking a Return Trip From Revit to Excel Using Dynamo

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.

2015-06-11_12-33-39

 

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.

2015-06-11_12-33-50

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:

2015-06-11_12-46-17

 

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.

2015-06-11_12-56-52

 

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.

2015-06-11_14-18-21

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.

2015-06-11_13-24-16

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.

2015-06-11_13-27-55

 

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!

2015-06-11_13-35-13

 

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.

2015-06-11_13-44-43

 

And to finish up, our results: