Customising the Backend of SP.Writer

There is a great tool that I use all the time from BIM Source called SP.Writer. I mostly used the Excel tool for consolidating parameter files that forked between structure and MEP at some point back in time. Using SP.Writer saved me quite a lot of headache in the process.

The problem is however that the Excel file is setup for out of the box shared parameter categories, so if you aren’t running OOTB shared parameter categories, the tool doesn’t work. There used to be an instructional video on how to modify the Excel file on youtube, but it’s long since been deleted and the promises of a new video haven’t come through.

Note: When I originally wrote this back in Novemeber, there was still no video. There is now a new video available to show you how to modify the file.

Here is how you modify that SP Writer file.

First you need to add categories to the settings tab within the excel file. This is the easy bit. It’s hidden by default and you need to unhide it.

Right click on the visible tab, select unhide

1

Select the settings tab and click OK.

2

The list of parameters are at the top, the Group ID should reflect your shared parameter categories, you’ll need to insert rows to add all the parameters.

3

Next, you need to open the macro / vba editor using the short cut ALT + F11 once open, on the left hand side of the screen under modules, find the module name mod_Write

4

In the mod_Write code, you want to edit the section highlighted.

5

The basics of it though is that this code determines what is written to the shared parameter file, for example the line that forms the header of your shared parameter file is created by the following code

“GROUP” & vbTab & “1” & vbTab & “Dimensions” & vbCrLf

and in your shared parameter file it becomes

GROUP 1              Dimensions

The text inside the quotes “ “ is written to the file, the & adds to the string, the vbTab is inserting a tab and the vbCrLf creates a new line.

Say for example that you wanted to add a new group number 56 named Ceilings, you would write the following code:

“GROUP” & vbTab & “56” & vbTab & “Ceilings” & vbCrLf

You’ve also probably noticed that at the end of each line there is an ampersand (&) and an underscore (_), the ampersand adds to the string and the underscore allows you to create a new line within the code to make the code cleaner, the problem is you are limited to creating 25 new lines in your code, so if you have a lot of shared parameter categories when you reach the 26th line you will see the following error

6

If you run into this, you need to consolidated some of these lines, but here comes the trick bit; as well as being limited to 25 lines, you’re also limited to 1023 characters per line so make sure you keep within these restrictions.

If you are removing a new line consolidating into a single line, don’t forget that you need to remove the underscore from the consolidated line.

Make sure that the category names and numbers match between the Settings tab in excel and the mod_Write module in VBA.

The next step is to find UserParameter under the Forms section, right click and select View Code

2015-03-13_10-08-42

You will see the same parameter category information in a slightly different format, once again you need to make sure that this matches your shared parameter categories that you’ve just entered under mod_Write. The format is

ElseIf Me.boxGroup.Value = “Category Name” Then
wsData.Cells(LastRow, 7).Value = 21

Once you have covered all that you should be right to go!

You can grab the SP.Writer tool from BIM Source here

One thought on “Customising the Backend of SP.Writer

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.