Updating a Workbench Project and Parameters from Excel Using AIM

This example is identical to the previous example but interacts with ANSYS AIM. As in the previous example, this example demonstrates the mechanics of interacting with Microsoft Excel from within a script and connecting ANSYS AIM operations to UI Control events issues by Microsoft Excel (or other similar interfaces). After you extract the ScriptingGuideExamples.zip file, you will find the files for this example in the AIM_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:

  1. Set the input parameter values within an Excel Workbook.

  2. Press an 'Update AIM' button in Excel.

  3. See an 'Updating ...' message in Excel as the calculation proceeds, and the output parameter value updated within the Workbook when the calculation is complete.

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

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

1 	SetScriptVersion(Version="16.0.260")
2 	Reset()
3 	# IronPython imports to enable Excel interop
4 	import clr
5 	import os
6 	clr.AddReference("Microsoft.Office.Interop.Excel")
7 	import Microsoft.Office.Interop.Excel as Excel
8 
9 	# Define working directory 
10	workingDir = r"D:\ScriptingGuideExamples\AIM_Excel_Parameter_Scripting\"
11	def updateHandler():
12	    
13	   # Update can take long, so disable the Excel warning -
14	   # "Excel is waiting for another application to complete an OLE action"
15	   ex.Application.DisplayAlerts = False
16
17	   # Define key ranges in the Workbook
18	   lengthCell = worksheet.Range["A3"]
19	   loadCell = worksheet.Range["B3"]
20	   defCell = worksheet.Range["C3"]
21	   
22	   # Get the Workbench Parameters
23	   lengthParam = Parameters.GetParameter(Name="P1")
24	   loadParam = Parameters.GetParameter(Name="P2")
25	   defParam = Parameters.GetParameter(Name="P3")
26	   
27	   # Assign values to the input parameters
28	   lengthParam.Expression = lengthCell.Value2.ToString() + " [cm]"
29	   loadParam.Expression = loadCell.Value2.ToString() + " [N]"
30	   
31	   # Mark the deformation parameter as updating in the workbook
32	   defCell.Value2="Updating..."
33
34	   # Run the project update
35	   Update()
36
37	   # Update the workbook value from the WB parameter
38	   # Access the deformation value in "m"
39	   defValue = defParam.Value.ConvertUnit("m")
40	   defCell.Value2 = defValue.Value
41
42	   # restore alert setting
43	   ex.Application.DisplayAlerts = True
44
45	# Open the Workbench Project
46	if (os.path.exists(os.path.join(workingDir, "AIMExcelParameterScripting.wbpj"))):
47	   Open(FilePath = os.path.join(workingDir, "AIMExcelParameterScripting.wbpj"))
48	else:
49	   RunScript(FilePath = os.path.join(workingDir,"AIMExcelParameterScriptingProject.wbjn"))
50
51	# Open Excel and the workbook
52	ex = Excel.ApplicationClass()
53	ex.Visible = True
54	workbook = ex.Workbooks.Open(os.path.join(workingDir , "ParameterExample.xlsx"))
55	worksheet=workbook.ActiveSheet
56
57	#Apply the update handler to the workbook button
58	OLEbutton = worksheet.OLEObjects("CommandButton1")
59	commandButton = OLEbutton.Object
60	commandButton.CLICK += updateHandler
61
62

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 3-7

The clr module is imported to enable IronPython to load and interact with CLR modules from other applications. In this example we add a CLR reference to the Microsoft Excel Interop assembly, and then import the module as the Excel namespace. The 'os' module used in this script provides capabilities for interacting with operating system services. Refer to the Python Standard Library documentation for details on all available modules.

Line 10

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 11-43

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 58 of this script.

Lines 17-20

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 22-25

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

Lines 27-29

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

Lines 31-32

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

Line 35

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

Lines 37-40

When the Update is complete, we update the output cell value in the Workbook with the parameter value in the Project.

Note the reference to defparam.Value. The Value property of a parameter can have different types (Numeric, String, Boolean, Quantity, etc.). In this instance, it is a Quantity, which in turn has properties for its Value and Unit. For consistency, we can access the value in a specific unit system (independent of the Project unit system). So, in this case, we access the result value in "m", by using ConvertUnit method.

Lines 46-49

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 51-53

Using the imported Excel namespace, we create an instance of the Excel application and make it visible.

Lines 54-55

We open the Excel workbook that contains the parameter table and 'Update' button, and get a reference to the primary (active) worksheet in the book.

Lines 57-59

We 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 60

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.


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