Using Linked Excel Worksheets

Aurora provides a linking feature to update data using Excel worksheets. The input data resides in an Excel workbook and is linked into the input dataset. The data is updated through the Excel worksheet and automatically read into Aurora ready for use in a study.

NOTE: Energy Exemplar cannot guarantee that Change Sets will work with linked tables (Excel worksheets). Change Sets can only work with a linked table if the worksheet contains a "Primary Key” column, all values within this column are unique, and the Primary Key values remain consistent with those referenced within the Change Set (the primary key values when the Change Set was created). Primary key values within an Excel worksheet cannot be automatically controlled from the Aurora interface; therefore, management and maintaining validity of the primary key values within liked tables is the responsibility of the user.

Requirements for linking an Excel worksheet into Aurora

To link an Excel worksheet into Aurora the format of the linked table must match the format of the existing input database table.
No spaces or special characters are allowed in the name of the linked worksheet.
All the labels in the header row must be text. Columns with missing headers will be ignored and will not appear in Aurora.

NOTE: If the column headings in Excel are numeric, you will need to change them to text by placing a single quote (') mark at the beginning of the cell value. The text format can be shared with all the header columns by dragging the lower right corner of the new text cell to the end of the table. All the numeric values in the columns should now be left-justified.

Table may not have a sub-header row.
If a column contains a text value all the values in the column must also be text.
Dates in an Excel worksheet must be formatted as text.
Cells that contain calculations of formulas must be formatted as text.

Creating a table in Excel

To ensure a table is formatted properly, follow these steps.

Open an Aurora project file.
Select the Input Data button on the Home Tab ribbon to open the Input Data Assumptions window.
Select the desired input table and double-click to open the table.
Select the entire data grid by clicking on the top left corner. Right-click and choose Selection To Excel (wHeaders) from the menu.
Microsoft Excel will open and populate a worksheet with the selected table.

Formatting the data within the Linked worksheet

Select the tab for the table and name the Excel worksheet (no spaces or special characters in the name of a linked worksheet).
Name and save the Excel workbook.
The header row in the table contains only text characters so the format for the header row is text. If the column headings in Excel are numeric, you will need to change them to text by placing a single quote (') mark at the beginning of the cell value.
If a column contains both text and numeric values all the values in the column must be converted to text.

NOTE: When an Excel worksheet is linked to an Access database, the Jet database engine uses the data in the cell to determine the type of data being linked. If the data type in a column changes from numeric to text or vice versa, the link will fail and not populate the linked table with all the data.

Changing a numeric value to text

Select the cell that contains the value.
The F2 function key will allow you to edit the value in the selected cell.
Place the cursor in front of the value in the cell.
Enter a single quote (') mark directly in front of the numeric value. The entry will change to a left justified text value.
To change a selected set of numeric records at once, use the "Change Column Type in Excel” macro (contact Support to get a copy of this file).

Using the "Change Column Type in Excel” macro

Open "Change Column Type in Excel” macro.
Copy selected data from the table.
Paste the data into the macro.
Select the data to be changed to text in the macro and click the Change Column Type button in the macro.
Copy the text data and paste it back into the Excel worksheet.

Using the "Change Column Type in Excel” Macro to change date fields

Open "Change Column Type in Excel” macro.
Copy date fields in the linked worksheet.
Paste the selected date fields into "Change Column Type in Excel” macro.
Select the date fields and click the Change Column Type button in the macro.
Copy the text date fields and paste them back into the Excel worksheet.

Cells that contain formulas, calculations or references must be text

In Excel, place the cursor in the cell that will contain the formula or calculation.
Select the Function option on the toolbar and select the More Functions option from the list.
Select the TEXT format option from the function list.
Select the OK button to apply the TEXT format to the cell.
Place the cursor in the Value field and navigate to the worksheet and cell that contains the formula or calculation. The location and cell will populate in the Value field on the Function Arguments window.
Place the cursor in the Format_text field and enter the format for the numeric display. The format requires that it be contained in quotes. No commas, $ or other special characters are allowed in a linked cell.
Select the OK button to populate the Excel worksheet with the text formula, calculation or reference.

Tutorials

Using Linked Excel Worksheets