Exchanging Data Between Microstran and a Spreadsheet

Exchanging data between Microstran and a spreadsheet program can be very useful in some situations. For example, you might want to calculate node coordinates in a spreadsheet and then transfer them to Microstran without having to type them in. This is a simple procedure and an example is presented below to illustrate the necessary techniques.

The steps we are going to follow are:

1. Create a parallel-chord truss using Standard Structures Input.
2. Sort the nodes so the nodes in each chord are consecutively numbered.
3. Copy the range of X coordinates from the node table in Microstran's Table Input to the Windows clipboard.
4. Paste the range of X coordinates from the clipboard to the spreadsheet.
5. Enter the desired parameters into the spreadsheet, which then calculates the Y coordinates of the chords for a trussed arch. Click here if you want to download the Microsoft Excel file for this spreadsheet (.XLS - 24K).
6. Paste the ranges of Y coordinates back into the Microstran node table.
7. Exit from Table Input, saving the changes.

1. Create Parallel-Chord Truss

Entering the parameters shown in the dialog box produces the truss shown below:

2. Sort Nodes

In order to have the nodes in each chord listed in a separate range of cells in the spreadsheet, it is necessary for them to be consecutively numbered. This is easily achieved with the Renumber command, using the default sort settings in the dialog box below.

3. Copy Range of X Coordinates to Clipboard

The screen shot below shows the node table in Microstran's Table Input with the range of X coordinates selected, ready for copying. To copy this range to the "clipboard" press Ctrl+C .

4. Paste Range of X Coordinates into Spreadsheet

In the spreadsheet program, select the X coordinates column and click on the button (or press Ctrl+V) to "paste" the range of X coordinates from the clipboard to the spreadsheet.

5. Input Parameters in Spreadsheet

In the spreadsheet, the span is automatically calculated as the maximum X value. Enter the truss depth at the supports and at the center, and the rise in the top chord. The spreadsheet will then calculate the corresponding Y coordinates for the top and bottom chords. The graph embedded in the spreadsheet provides a check on the calculated geometry. Note the formula for cell C27 displayed at the top of the spreadsheet. This formula, in each of the top chord cells, creates a parabola for laying out the top chord nodes but any other suitable formula may be used if required. A similar formula is used for the bottom chord Y coordinate cells. See above if you want to download the Microsoft Excel file for this spreadsheet.

6. Paste Ranges of Y Coordinates Back to Microstran

The screen shot above shows the range of Y coordinates for the top chord selected in the spreadsheet, ready for copying. To copy this range to the clipboard, click on the button (or press Ctrl+C). Select the corresponding range of Y coordinates in the Microstran node table and press Ctrl+V to paste the new coordinates back into the structure. The view of the structure changes to show the new coordinates when you exit from Table Input, saving the changes. Below is a screen shot showing the finished structure.