If you want to refer to input data from another STEM model, the easiest way is to open the other model in the Editor, then click with the mouse to pick up the external reference, just as you would for an internal reference. See 4.13.4 Linking STEM model inputs to the results of another model for further information.
For other sources, you must type the <reference> text into a formula by hand, but you can use the External Links dialog to format the filename part correctly. The following pages describe this procedure for Excel files, which also applies to links to most database files – see 4.13.5 Creating an explicit ODBC link for exceptions and links to ODBC data sources and database servers.
The easiest way to link STEM and Excel together is to start by exporting model data from STEM – see 6.2 Exporting model data to Excel and 6.4 Exporting results data to Excel.
Pasting an external reference from the External Links dialog
Suppose, for example, you want to link the Capital Cost of a Resource to cell
$A$2
in the spreadsheet
C:\DATA.COSTS.XLSX:
- Select Costs from the Resource icon menu. The Costs dialog appears.
- Select the Capital Cost field with the mouse or cursor keys.
- Select Links… from the File menu on the main Editor window. The External Links dialog is displayed.
- Select the Add… button to display the Define External Link dialog.
- Enter C:\DATA.COSTS.XLSX
and click OK. The file is added to the list of Links in the External Links dialog.
Note: If you prefer to browse for the required file, you can click the Browse… button on the Define External Link dialog to display the Select External File dialog, a standard Windows file-selection dialog.
- Select the Paste button. The Select Name dialog is displayed, listing all the named ranges defined in the selected workbook.
- Select the required name from the list and click OK, or type any other reference, e.g.,
$A$2 and press <Enter> to complete the formula. The appropriate external reference is pasted into the current formula bar.
Selecting an external reference to an Excel workbook
Note: You can enter an unlisted name if you haven’t yet created the name to which you wish to refer in the workbook.
It is also possible to retrieve time-series data or default values for a whole model element from an appropriately formatted spreadsheet or database – see 4.13.3 Finding and removing external links and 6. Exchanging data.
Creating an external link from the Formula Bar
Although the Paste button in the External Links dialog provides the easiest way to create an external reference, it is also possible to enter a filename directly in the formula bar. In the example above, you could skip steps 3 to 7 and simply type
'C:\DATA\COSTS.XLSX'!$A$2
in the formula bar.
If there is already an entry for this file in the External Links dialog, the new formula will share the same link, together with the existing choice for absolute/relative path. Otherwise, a new link will be created, which will have an absolute path by default.