Page 169
6.3 Exercise 19: TEDDS Excel Link
The TEDDS Excel Link allows you to link your TEDDS calculations with Microsoft Excel spreadsheets by
transferring data from TEDDS to Excel and from Excel back to TEDDS. Please note the Excel link
requires at least Microsoft Excel 2000. The calculations used in the following simple example can easily
be written in TEDDS without the need for the Excel link, the purpose of the example is simply to
demonstrate the transfer of data between TEDDS and Excel.
6.3.1 Step 1 - Create the Spreadsheet
¾
Open Microsoft Excel and create a new blank workbook with a single worksheet.
¾
Rename the worksheet TieDesign
¾
Type the following text to create a simple spreadsheet for a steel section tie design
If you enter appropriate values into the top four cells in column B then the remaining cells will update
with the calculated results. Now that you have created a simple example you can link this spreadsheet
to TEDDS. Before continuing save the workbook.
¾
Save the workbook as “\My Documents\TEDDS\Excel\Steel Tie Design.xls”
6.3.2 Step 2 - Create the TEDDS Excel Link Field
¾
Either open the “Steel Tie Design – with Input Table.doc.” that you created in the previous
exercise and delete the formulae for Design Strength, Minimum gross section area and Minimum
radius of gyration OR.
¾
Open a new blank document in TEDDS for Word.
¾
Type the following calculations which will define the input for your example spreadsheet. Use
Input Fields or if you like.
TIE DESIGN
Minimum yield strength; F
y
= 275 N/mm
2
Tie force; T
f
= 450 kN
Effective length; L
e
= 5 m
Slenderness limit; λ
l
= 300
Page 170
¾
Ensure that your cursor is on the line beneath ‘Slenderness limit; ……’
¾
Open the Insert TEDDS Field dialog and select the Excel tab.
¾
In the filename box enter the path of the Steel Tie Design.xls workbook that you created
earlier using the browse button to locate the saved file.
¾
Click the button Insert Example Link Sheet into Workbook. This will open the example
workbook in Excel and insert a TEDDS Excel link worksheet which includes instructions on how
to link your spreadsheet to TEDDS. Save the workbook and close Excel.
¾
Leave the other options at their default settings and click OK. TEDDS will insert a new field into
your document that when calculated will open your workbook and perform the necessary linking.
¾
Type the following at the end of the document to display the results of the spreadsheet
calculations:
TIE DESIGN
Minimum yield strength; F
y
= 275 N/mm
2
Tie force; T
f
= 450 kN
Effective length; L
e
= 5 m
Slenderness limit; λ
l
= 300
;
Design Strength; F
a
= ?f1 N/mm
2
Minimum gross section area; A
MIN
= ?f2 cm
2
Minimum radius of gyration; r
MIN
= ?f2 cm
Page 171
6.3.3 Step 3 - Create the Link Tables
You need to define which values will be transferred between TEDDS and Excel.
¾
Go back to the Steel Tie Design workbook that you created earlier
¾
View the additional worksheet TEDDS2XL.
This worksheet defines a list of variables that will be transferred from TEDDS to Excel and a second list
of variables that will be transferred from Excel back to TEDDS. Read the documentation included on the
worksheet which explains in more detail how the lists should be defined.
The Variables from TEDDS table defines the variables that will be read from your TEDDS document.
When the TEDDS Excel link starts the value of each variable will be inserted into the value column
automatically by TEDDS.
¾
Modify the table Variables from TEDDS so that it defines the input variables as follows:
You now need to modify your example worksheet to use the input values.
¾
Open the TieDesign worksheet
¾
Enter references in the first four rows of column B that refer to the value column of the
Variables from TEDDS table you just created.
In order to complete the linking process you now need to define the values that you want to write back
to your TEDDS document as variables, i.e. the results of the spreadsheet calculations.
¾
Go back to the TEDDS2XL link worksheet.
Page 172
The Variables back to TEDDS table defines the variables that will be assigned by TEDDS when the
Excel link is finished, the value of each variable will be read from the worksheet and written to the
TEDDS document. In this example you have created cell references that refer to the calculated results
in the TieDesign worksheet.
¾
Modify the Variables back to TEDDS table so that it defines the output variables as follows:
¾
Save your workbook and close Excel.
6.3.4 Step 4 - Running the Example
The link between TEDDS and Excel is now complete and you are ready to run your example.
¾
Use the Calculate All command to calculate the TEDDS document.
TEDDS will read the input variables you have defined in your document and copy the values to the
Excel workbook. Excel will then update the tie design calculations based on the input data. TEDDS will
then copy the calculated results back to your document. Finally the remaining expressions in your
document will output the calculated results.
TIE DESIGN
Minimum yield strength; F
y
= 275 N/mm
2
Tie force; T
f
= 450 kN
Effective length; L
e
= 5 m
Slenderness limit; λ
l
= 300
;
Design Strength; F
a
= 165.0 N/mm
2
Minimum gross section area; A
MIN
= 27.27 cm
2
Minimum radius of gyration; r
MIN
= 1.67 cm
