Excel spreadsheets in connection design

Tekla Structures
2019i
Tekla Structures

Excel spreadsheets in connection design

You can use Excel spreadsheets in connection design for all steel connections that have the Design or Design type tab in the connection dialog box.

You can link connections to Excel spreadsheets by selecting Excel in the External design option on the Design or Design type tab. The connection information is transferred to the connection type-specific spreadsheet where the needed calculations are made. The calculated properties are saved to an output file and the modified component property values are transferred back to the connection. The connection is modified according to the changes.

You can create an Excel spreadsheet for a connection type using the component_template.xls file available in the ..\Tekla Structures\<version>\Environments\common\exceldesign folder, or use a predefined file.

Files used in Excel spreadsheet connection design

The following files are used in the connection design with Excel spreadsheets:

File Description
Visual Basic script file The Excel.vb file links Tekla Structures with the external software and defines the Excel spreadsheet file names and the locations. The file is located in the ..\Tekla Structures\<version>\Environments\common\exceldesign folder.

Excel searches for the relevant spreadsheet file in the following order:

  1. From the \exceldesign folder in the current model folder: file named as component_ + number or name + .xls , for example, ..\test_model\exceldesign\component_144.xls.
  2. From the location defined with the XS_EXTERNAL_EXCEL_DESIGN_PATH advanced option as follows:

    XS_EXTERNAL_EXCEL_DESIGN_PATH (=%XS_DIR%\environments\common\exceldesign\) + "component_" + number + ".xls"

Component type-specific Excel spreadsheet The component type-specific spreadsheet contains predefined calculations. When you run the connection design, the connection properties and information of the main and secondary parts are transferred to the Input and Component sheets of the Excel spreadsheet.
Connection specific result file

The result file contains the modified connection properties.

  • The result file is created automatically from the Calculation sheet.
  • The file is stored in the \exceldesign folder in the model folder and named with the GUID, Globally Unique Identifier.
  • The file is updated each time you modify the connection.
  • The calculation results can be stored as an Excel spreadsheet, or in HTML or PDF format, depending on how the calculation spreadsheet is configured.
Template spreadsheet The ..\Tekla Structures\<version>\Environments\common\exceldesign folder contains a component_template.xls spreadsheet you should use to create your own spreadsheet applications to use with Tekla Structures components.

Example of an Excel spreadsheet in connection design

The images in this example show the Excel spreadsheet that is used for the End plate (144) connection.

The sample spreadsheet has the following sheets:

The Calculation sheet contains a report of the calculations.

The Inputs sheet contains the properties of the connection from the connection dialog box.

The Outputs sheet contains the design results. These values are transferred to the connection and the connection in the model is modified accordingly.

The Component sheet contains calculations, information on the connection geometry, and on the main part and the secondary parts. The component attributes in the spreadsheet are the same as in the corresponding .inp file. See more about .inp files in Input files (.inp files).

The following sheets are also included:

  • Data shows catalog information.
  • Norm shows the code selection and references to codes.
  • Language shows translations.

Example of visualizing the Excel connection design process

You can define in the Excel.vb file how the Excel connection design process is visualized. The Excel.vb file links Tekla Structures with the external software and defines the Excel spreadsheet file names and the locations.

  1. Open the Excel.vb file located in ..\Tekla Structures\<version>\Environments\common\exceldesign.
  2. Configure the Excel.vb file as follows:

    • Visualizing control - Const DEBUG As Boolean= True
    • Visualizing using Excel - Const SHOW_EXCEL As Boolean = True
    • Storing the output - Const STORE_RESULTS As Boolean = True
  3. Save the file.
  4. Click the Applications & components button in the side pane to open the Applications & components catalog.
  5. Search for Clip angle (141) and double-click it to open the properties dialog box.
  6. On the Design type tab:
    1. Select Excel in the External design option.
    2. Enter the load values.

  7. Click Modify.

    The Excel design file opens and shows the Inputs sheet.

  8. Click OK to continue.

    The Excel design process now calculates the data that is shown on the Component sheet, and then opens the Component sheet.

  9. Click OK to continue.

    The Excel design process now calculates the result output values and then opens the Outputs sheet. The calculated results values are transferred to the connection.

  10. Save the file in the model folder.
  11. When you click OK , the design process is completed and the Excel design file is closed.

Showing connection status in Excel connection design

When you use Excel spreadsheets in connection design, you can have Tekla Structures use different colors in component symbols to show the status of a component in the model.

You can do this by including an error attribute on the Outputs sheet of the component's Excel spreadsheet. The type of the attribute is int.

The possible values are:

Value Color Status

1

Green

Bolt edge distances are sufficient.

The connection passes the connection design check using the UK and US design codes embedded in the system.

2

Yellow

Bolt edge distances are insufficient according to the value defined in the Components settings in File > Settings > Options.

3

Red

Tekla Structures cannot calculate the component properties. The possible reasons are:
  • The connection direction is not correct.
  • The work plane is not correct.
  • The selected connection is not appropriate for the situation.
  • The connection design check was carried out using the embedded UK and US design codes and the connection cannot support the loading you have defined.
Note:

The component symbol color can only be controlled for system components, not for custom components.

Was this helpful?
Previous
Next