Edit Model Inputs in Excel

46.1 Edit Model Inputs in Excel

When you want to edit a large number of model inputs, it is often most efficient to do so via Excel. The process works as follows:

Export the desired inputs from TreeAge Pro to an Excel worksheet.
Edit the values in the Excel worksheet.
Import the new values back from Excel to the TreeAge Pro model.

This option is available for variables, trackers, distributions and tables. Each input is described in the next few sections and uses the Special Features > Excel tutorial example model TreeWorkbookTest.trex.

Updates from Excel are applied to the active model. Therefore, you must select the correct model in the Tree Diagram Editor before sending updates back from Excel to TreeAge Pro.

46.1.1 Edit variables in Excel

In the Variables View toolbar's the To Excel icon/function will be enabled.

To edit variables in Excel:

Open the Variables View.
Click the Open in New Excel Spreadsheet toolbar icon.

The tree's variables are then exported to an Excel worksheet.

In the worksheet, the original variables are identified by hidden column I. This allows you to rename variables based on the current name. Hidden column H is used to determine which variables have been modified. We recommend that you do not modify the data in either hidden column.

Note that if you add new variables, you will not want to copy the original name in the hidden column. If you do, the existing variable will be replaced by the new variable.

Within the worksheet, you can change variable properties and default definitions (at the root node).

To send the new properties/values back to TreeAge Pro:

Select the correct tree in the Tree Diagram Editor.
In Excel, choose the Add-ins ribbon then choose TreeAge > Add or Update Variables from the Excel menu.

Note that Excel's presentation of add-in options changes with each new version of Excel. Excel for Mac also presents add-ins differently. Despite the presentation differences, the functions should work the same way.

The variables in TreeAge Pro will be updated with the new properties/values from the Excel worksheet. There is no need to keep the Excel worksheet after the updates are complete as it can be regenerated at any time.

To refresh the data in the worksheet from the values in TreeAge Pro:

In Excel choose the Add-ins ribbon then choose TreeAge > Refresh Variables from the Excel menu.

Refresh will update the existing list of variables with the current values from TreeAge Pro.

If you have added new variables within TreeAge Pro, please generate a new list via Edit in Excel from within TreeAge Pro.

If you export variables to Excel at a node other than the root node, you are asked whether to include an additional column of data for the variable definitions at the selected node. If you answer "yes", then the variable definitions at the selected node can also be updated and sent back to TreeAge Pro.

46.1.2 Edit trackers in Excel

Editing trackers in Excel works the same as editing variables, although tracker properties are different from variable properties.

In the Trackers View toolbar the To Excel icon/function will be enabled.

To edit trackers in Excel:

Open the Trackers View.
Click the Open in New Excel Spreadsheet toolbar icon (highlighted below).

The tree's trackers are then exported to an Excel worksheet.

Within the worksheet, you can change the tracker properties and the initial values. Hidden columns are used in the same way as the variables worksheet. See note in prior section.

To send the new properties/values back to TreeAge Pro:

Select the correct tree in the Tree Diagram Editor.
In Excel choose the Add-ins ribbon then choose TreeAge > Add or Update Trackers from the Excel menu.

The trackers in TreeAge Pro will be updated with the new properties/values from the Excel worksheet. There is no need to keep the Excel worksheet after the updates are complete as it can be regenerated at any time.

To refresh the data in the worksheet from the values in TreeAge Pro:

In Excel choose the Add-ins ribbon then choose TreeAge > Refresh Trackers from the Excel menu.

Refresh will update the existing list of variables with the current values from TreeAge Pro. If you have added new variables within TreeAge Pro, please generate a new list via Edit in Excel from within TreeAge Pro.

If you export trackers to Excel at a node other than the root node, you are asked whether to include an additional column of data for the tracker modifications at the selected node. If you answer "yes", then the tracker modifications at the selected node can also be updated and sent back to TreeAge Pro.

46.1.3 Edit distributions in Excel

If you have the Excel Module licensed, the Distributions View toolbar's To Excel icon/function will be enabled.

To edit distributions in Excel:

Open the Distributions View.
Click the Open in New Excel Spreadsheet toolbar icon (highlighted below).

The tree's distributions are then exported to an Excel worksheet. See note on hidden columns in Variables section above.

Within the worksheet, you can change the distribution properties and parameters. Note that the Help/Explanation column provides details on how the parameters are used for the appropriate distribution type.

To send the new properties/parameters back to TreeAge Pro:

Select the correct tree in the Tree Diagram Editor.
In Excel, choose the Add-ins ribbon then choose TreeAge > Add or Update Distributions from the Excel menu.

The distributions in TreeAge Pro will be updated with the new properties/parameters from the Excel worksheet. There is no need to keep the Excel worksheet after the updates are complete as it can be regenerated at any time.

A few distribution types have additional options that are not updated through the Excel module (e.g., Fractile Gaussian vs. Swanson). These options must be edited in the Add/Change Distribution dialog.

46.1.4 Edit tables in Excel

Variables, trackers and distributions are edited within a list. However, tables are edited individually in Excel since they contain both properties and data.

If you have the Excel Module licensed, the Tables View toolbar's Open in New Excel Spreadsheet icon/function will be enabled.

To edit a table in Excel:

Open the Tables View.
Select a single table.
Click the To Excel toolbar icon (highlighted below), which exports the table to an Excel worksheet.

Within the worksheet, you can change the table properties and data. Be careful not to change the structure and/or location of the data in the worksheet.

To send the new properties/data back to TreeAge Pro:

Select the correct tree in the Tree Diagram Editor.
In Excel, choose the Add-ins ribbon then choose TreeAge Eclipse > Add or Update Distributions from the Excel menu.

To refresh the data in the worksheet from the values in TreeAge Pro:

In Excel, choose the Add-ins ribbon then choose TreeAge Eclipse > Refresh Trackers from the Excel menu.

Note that you can add columns to the table in Excel by adding data to the right of the existing columns. Value columns can have custom headings, but the Index column heading should not be changed.