As promised in this post, I have written and documented Excel User Defined Functions (UDFs) to interface with all the Strand7 API functions that return node attributes or results.  These 22 functions allow node attributes and results to be read directly from an Excel spreadsheet.  Examples and full open source code are included in the download file: GetNode.zip.

To use the UDFs it is necessary to have a licensed copy of Strand7 and the API installed.  The spreadsheet may then be linked to up to 12 Strand7 data and results files through the Control Panel sheet:

Add files to file list

Connect to listed files with the “Open Data” button

Function input and output is documented on the “NodeFunc” sheet:

Node Function input and output (click for full size view)

Node Results output details

Further details of the input and output for each function are given in the API Manual.  Manual page numbers are given in the UDF function list below:

Node Function List

The functions may be used either by entering the functions directly, in the same way as built in Excel functions, or the GetNodeFunc function may be used to call a function with the name given in a separate cell.  Examples of each usage are shown in the screen shot below:

Examples of GetNodeFunc and S7NodeUCS functions

All the functions return an array of results, and must be entered as an array function. Details of the procedure for entering array functions are given here.

The function results array returns results for all the selected node numbers specified in a single column range.  Additional parameters (where required) are specified in a separate single column range (with 1-3 rows).  Each parameter may be specified as a single value, or as a range address.  Where the range method is used the range must be the same length as the node list range.  Examples of this method are shown in the screenshot below:

Function input parameter (ResultCase) specified as a range

For example purposes the number of node results has been kept to a small number, but the functions will return results from large files quickly and efficiently.  For example, the two screenshots below were generated from the Strand7 sample “Suspension Bridge.st7” file, which has 3528 nodes.  The first image was plotted from X,Y coordinates using the standard coordinate system.  For the second a new User Coordinate System was created, and the coordinates in this system were read from Excel using the S7NodeUCS function:

UCS1 XY coordinates

UCS2 XY coordinates

The examples in the screenshots above (other than the suspension bridge) were all generated from the file Raft1.st7 included with the download file.  Note that this file is for illustration purposes only, and many of the node attributes created for Load case 2 and Freedom case 2 are not appropriate to the non-linear static analysis actually carried out (which uses Load and Freedom cases 1 only).  The spreadsheet may be easily linked to any other file(s), for instance links to a selection of the files in the  “Tutorials/Help Topics Example Models” folder is shown below:

Link to Strand7 Example Model files