Updating a Workbench Project and Parameters from Excel

This example demonstrates the mechanics of interacting with Microsoft Excel from within an Ansys Workbench script, and connecting Ansys Workbench operations to UI Control events issues by Microsoft Excel (or other similar interfaces). The physics in this example is a simple cantilever beam, where the beam length and load are input parameters, and the resulting deflection is an output parameter. The case is defined within Ansys Workbench as a Static Structural system with the appropriate parameters created. After you extract the ScriptingGuideExamples.zip file, you will find the files for this example in the Excel_Parameter_Scripting directory.

Of primary interest in this example is that user interaction with this project will be done via Microsoft Excel, where you will:

Set the input parameter values within an Excel Workbook.
Click the Update Workbench button in Excel.
See an Updating... message in Excel as the calculation proceeds.

The output parameter value are updated within the Workbook when the calculation is complete.

When the Ansys Workbench script below is executed, it opens the necessary Ansys Workbench project and Excel Workbook, and then it establishes an event connection so that an Ansys Workbench script function is executed at the press of the button within Excel.

Workbench Script

The script for this example follows. Each line is numbered for reference in the discussion that follows.

1 	# IronPython imports to enable Excel interop
2 	import clr
3 	clr.AddReference("Microsoft.Office.Interop.Excel")
4 	import Microsoft.Office.Interop.Excel as Excel
5 
6 	workingDir = AbsUserPathName("ScriptingGuideExamples/Excel_Parameter_Scripting/")
7 
8 	def updateHandler():
9 	    
10	   # Define key ranges in the Workbook
11	   lengthCell = worksheet.Range["A3"]
12	   loadCell = worksheet.Range["B3"]
13	   defCell = worksheet.Range["C3"]
14	   
15	   # Get the Workbench Parameters
16	   lengthParam = Parameters.GetParameter(Name="P1")
17	   loadParam = Parameters.GetParameter(Name="P2")
18	   defParam = Parameters.GetParameter(Name="P3")
19	   
20	   # Assign values to the input parameters
21	   lengthParam.Expression = lengthCell.Value2.ToString()
22	   loadParam.Expression = loadCell.Value2.ToString() + " [N]"
23	   
24	   # Mark the deformation parameter as updating in the workbook
25	   defCell.Value2="Updating..."
26
27	   # Run the project update
28	   Update()
29
30	   # Update the workbook value from the WB parameter
31	   defCell.Value2 = defParam.Value
32
33
34	# Open the Workbench Project
35	Open(FilePath = workingDir + "ExcelParameterScripting.wbpj")
36
37	# Open Excel and the workbook
38	ex = Excel.ApplicationClass()
39	ex.Visible = True
40	workbook = ex.Workbooks.Open(workingDir + "ParameterExample.xlsx")
41	worksheet=workbook.ActiveSheet
42
43	#Apply the update handler to the workbook button
44	OLEbutton = worksheet.OLEObjects("CommandButton1")
45	OLEbutton.Object.CLICK += updateHandler
46

Discussion

This example demonstrates a number of the typical programming constructs necessary for Ansys Workbench scripting to interact with the Common Language Runtime (CLR) API exposed by Microsoft Excel and similar applications.

Lines 1-4

The clr module is imported to enable IronPython to load and interact with CLR modules from other applications. In this example a clr reference to the Microsoft Excel Interop assembly is added and then the module is imported as the Excel namespace.

Line 6

The working directory is set, so that the script can load the Project and Excel Workbook from the specified location. When completing this example, this line will need to be modified to reflect your working location.

Lines 8-31

An updateHandler() function is created that performs all the necessary actions to interact with Excel and update the Ansys Workbench project. This function is connected to the CLICK event of the Excel button on line 45 of this script.

Lines 10-13

Specific cells of interest in the Workbook are created as named references in the script to facilitate later use of these cells. In this instance, the cells that hold the values of the two input parameters and one output parameter are given named references.

Lines 15-18

References to the three parameters exposed by the Static Structural system are also assigned to variables for later use.

Lines 20-22

The Expression property defining the input parameters are set based on the values of the associated cells in the Excel Workbook. The Value2 property is used because it has simpler interaction when working with a single cell value.

Lines 24-25

The script sets the value of the output parameter cell in the workbook to Updating... while the calculation proceeds.

Lines 27-28

The Update() command is executed to update the project based on the new input parameter values.

Lines 30-31

When the update is complete, the output cell value in the Workbook is updated with the parameter value in the project.

Note the reference to defparam.Value.Value. The Value property of a parameter can have different types, including Numeric, String, Boolean, and Quantity. In this instance, the type is Quantity, which in turn has properties for Value and Unit. Therefore defparam.Value.Value is the numeric part of the quantity that is the parameter's current value.

Lines 34-35

The lines following the updateHandler() definition are those first executed when the script executes. The first operation is to load the Workbench project containing the parameterized Static Structural analysis.

Lines 37-39

Using the imported Excel namespace, an instance of the Excel application is created and made visible.

Lines 40-41

The script opens the Excel workbook that contains the parameter table and Update button and gets a reference to the primary (active) worksheet in the book.

Lines 43-44

You get a reference to the named OLE button (CommandButton1) that is present in the worksheet. This button was added to the worksheet by inserting the control within Excel, but no other macros or code associated with this button is required in the workbook.

Line 45

The updateHandler() function is added as an event handler to the CLICK event on the command button. Whenever the button is clicked, the associated Ansys Workbench script function is executed.