Microsoft Office Excel

You can use Microsoft Office Excel 2013 or 2016 as a calculator in ANSYS Workbench via the "Microsoft Office Excel" component system. This feature exposes Excel ranges as input and output parameters to ANSYS Workbench; you can use these ranges to create design points and Design Exploration studies.

Note: The Excel add-in is included with ANSYS Workbench. It does not require DesignXplorer or any other specific ANSYS product, but it must connect to a locally installed seat of Microsoft Excel (purchased separately).

Using Excel with ANSYS Workbench Projects topics:

Preparing the Excel File

To be exposed as parameters in Workbench, the ranges must be named in Excel. The names are filtered during the addition of the Excel file to the project; only the ranges matching the prefix defined by the Named Range Key property in the Properties view of the Analysis cell are made visible in ANSYS Workbench.


Note:  By default, no filtering prefix is defined at either the Workbench or the project level. You set a filter by either of the following methods:

  • Set a default prefix that will be used for all new projects (see Microsoft Office Excel Options).

  • Set a prefix at the project level by entering it in the Named Range Key property in the Properties view of the Microsoft Office Excel Analysis cell.


To name a range in Excel:

  1. Open the file in the Microsoft Office Excel application.

  2. Right-click a cell and select the Define Name.

  3. Enter the name.

  4. Review and modify all the defined names in the Excel application using Formulas > Name Manager.

A named range in Excel can contain several cells; however, Workbench expects the named ranges to contain either one or two cells, the first cell being reserved for the value and the second cell being reserved for the unit string, if any. If a named range contains more than two cells, it is ignored.

You can review and modify all the defined names in the Excel application using Formulas > Name Manager.

A named range can contain several cells. Workbench expects the named ranges to contain either one or two cells, the first cell being reserved for the value and the second cell being reserved for the unit string, if any. If a named range contains more than two cells, it is ignored.


Note:  If you added the file to the Excel system in Workbench before naming the ranges, or if you want to edit the names of the ranges after the addition of the file, you can right-click the Analysis component or the file node in the Outline and select the Open file in Excel operation. This opens the Excel file used by Workbench in Excel.

All changes performed in Excel impact the state of the data in Workbench and invalidate the results; be sure to save your changes and reload the file afterwards (when editing the Analysis cell, right-click the file node and select Reload).


Setting Up the Excel Calculator

Adding a File

To add a file:

  1. Drag and drop a "Microsoft Office Excel" system template on to the Project Schematic.

  2. Right-click the Analysis cell, select Add File, and browse to the Excel file that you have prepared.

  3. Right-click the Analysis cell and select Edit Configuration to review the list of named ranges retrieved from the Excel file. For each listed named range, check the Input or Output column in order to publish the range as an input parameter or an output parameter in the Workbench project

  4. Return to the Project Schematic; the Microsoft Office Excel system is linked to the Parameter Set bar.

Then right-click the Analysis system and select Edit Configuration to review the list of named ranges retrieved from the Excel file. For each listed named range, check the Input or Output column in order to publish the range as an input parameter or an output parameter in the Workbench project. Return to the Project Schematic; the Microsoft Office Excel is linked to the Parameter Set bar.

Macro Property Usage

If the calculation in Excel requires the execution of a macro, select the added file in the outline and check its Use a Macro property in the Properties View. Then enter the name of the macro in the Macro Name property.


Note:  If a button is used to start the calculation and you do not know the name of the macro associated with it, right-click the button and select Assign Macro to discover the name to use.

The project is ready to create design points and Design Exploration studies.


Support of Units

Units are handled by the Excel system. A valid Workbench unit string must be used and included in the named range.

For instance, if an input parameter is a length in millimeters, you can name "WB_Length" the range A1:B1 where the cell A1 contains the length value (that is, "120.5") and the cell B1 contains the unit string ("mm"). When selecting the “WB_Length” range in the Outline view, the Properties view looks like below. In this case, the Quantity Name is automatically identified as a Length.

If there are several possible Quantity Names for the same unit string, you have to select the Quantity Name in the properties of the range for the unit conversion to be performed as expected. For instance, if A1:B1 was actually a temperature in degree Celsius, the Quantity Name could be a Temperature or a Temperature Difference.

For detailed information on working with units in ANSYS Workbench, see Unit Systems in the Working with Units section of the ANSYS Workbench help.

File management and modification of the worksheet

When the Excel file is added to the Analysis cell of the Excel system, it is copied inside the Workbench project files. So any modification made to the original file is not seen by Workbench except if you delete and add the file again.

To modify the file copied in the Workbench project files, right-click the Analysis component, or the file node in the Outline view, and select the Open file in Excel operation. Once modifications are done, save the file. The state of the file in Workbench changes to Refresh Required, which indicates that data are not synchronized anymore. Results such as design points and Design Exploration systems in the Workbench project are outdated. Refresh the project to synchronize all the pieces of the project.

If a change in the Excel file was not detected by Workbench, it is possible to force a reload of the file (right-click the file node and select Reload).

It is not necessary to close the workbook or the Excel application to proceed with design points or Design Exploration updates. Interaction with the Excel application will be frozen during such operations but you will be able to see the performed calculations.

Limitations

Version limitation

The Excel feature in ANSYS Workbench requires Microsoft Office Excel 2013 or 2016.

Platform limitation

The Excel feature is only available on Windows systems.

Excel Updates with "Set as Current" operation

In a project with retained design points, switching to a different up-to-date design point via the Set as Current menu option automatically updates the Excel system, which could be time-consuming if the spreadsheet contains macros or extensive calculations.

Design point failure when updating project with multiple Excel systems

If you have Office Excel 365, Office Excel 2013, or Office Excel 2016 installed on your machine and your Workbench project contains multiple Excel systems and the Excel process window is hidden, updating the project may cause design points to fail and the following exception message to display:

"We found a problem with this formula. We couldn't find a range reference or a defined name."

Workaround: Before running any update operations, open the Excel process window by right-clicking and selecting Open File in Excel.

This issue is documented by Microsoft. For more information, see https://support.microsoft.com/en-us/kb/3083825.

Troubleshooting Excel Use in ANSYS Workbench

You are requested to install the Multilingual User Interface Pack of Microsoft Office for your language If you run the 2007 or 2010 English version of Excel and the locale for the current user is configured for a language other than English, Excel will try to locate the language pack for the configured language. If the language pack is not found, then the error is reported and the automation of Excel, as used by the Excel as a Calculator feature, cannot be performed. To solve this issue, you have to install the Multilingual User Interface Pack of Microsoft Office for your language. As an alternative, you can also configure the locale for the current user to English.

This bug is documented by Microsoft. For more information, see http://support.microsoft.com/kb/320369.


Release 18.2 - © ANSYS, Inc. All rights reserved.