STEM can export data directly into Excel, generating spreadsheet data in the right format to readily link into STEM. This export feature is coupled with a link in the STEM model so that the exported data in Excel become the live interface for the relevant inputs and results. STEM automatically ensures that the right labels are in place and in the correct format (see 6.3 Linking input data from Microsoft Excel spreadsheets): this is much quicker than creating a data sheet manually with reference to the labels defined in the
model.xls
spreadsheet shipped with STEM.
Exporting input data and reference labels to Excel
Exporting to an existing spreadsheet
If you subsequently wish to export data for another element, the procedure from within STEM is the same as described above. STEM opens the existing workbook and simply appends a new sheet called STEM
n
where n
is the next available integer. (Clearly, you must close the Excel file before attempting to add further data to it).
Within Excel, if you wish to integrate the most recent named ranges into another sheet that you are working on, select the appropriate cells (making sure to include any named ranges in their entirety) then
cut
them from the sheet and paste into the new position as desired. It is important here to cut rather than
copy
the cells: if you simply copy and paste, Excel will transfer the labels and data but not
the named ranges (which will remain in the original sheet).
Similarly, if you later add further input data to an element for which you have already created Excel names, you can readily re-export the data for the element; STEM will move the named range onto the newly generated sheet. Some manual adjustments to the workbook may be required if the previous range was linked to other parts of the workbook.
Updating a STEM model from a linked spreadsheet
If you modify and save the data within a linked spreadsheet, STEM will ask whether to update the links when you next open or run the model. You can force an update at any time by clicking on the Update button on the External Links dialog. For more information on linking STEM models to Excel, see 6.3 Linking input data from Microsoft Excel spreadsheets.
Exporting data without linking to Excel
You can export input data and choose not to modify the model to link to the resulting spreadsheet. In essence, this is a way of obtaining a ‘print-out’ of the model in Excel. Follow the steps given above, but when prompted “Continue and modify model data to link to the spreadsheet?” click on
No. The new worksheet is created, but the STEM model remains completely independent of it.