![\"image\"](\"https:\/\/www.padtinc.com\/wp-content\/uploads\/oldblog\/image_117.png\" "\\"image\\"")
<\/a><\/p>\nSo I dragged it out on my project page and tried to use it\u2026 and got no where.\u00a0 My assumptions were not valid, it\u00a0 didn\u2019t work the way I expected because, it turned out, I was not thinking about how it fit into the project correctly.\u00a0 So I backed up, actually read the help (gasp!) and after a experimenting got it to work, I thought.\u00a0 Then I talked to some folks at ANSYS, Inc. and they reminded me of what this is: an Excel Solver System.\u00a0 It is\u00a0 not a tool that lets you \u201cdrive\u201d your project from Excel. It lets you use Excel to calculate values.<\/p>\n
This posting is a summary of what I learned. And as I was working through it I thought it would be good to also cover the python interface to Excel and how to save tabular information to Excel. These will be covered in future articles (hence this being Part 1).<\/p>\n
What You Need to Know<\/h2>\n
As I said above, my problem was that I was thinking about how Excel fit into my project all wrong.\u00a0 The first thing you should do is read the help on the Excel System.\u00a0 The best way to find it is type excel into search.\u00a0 The item with the most hits will take you to the article for component systems, then click on Microsoft Office Excel. (I wish I could just put a link in\u2026 grumble\u2026 grumble\u2026 ).<\/p>\n
To use the Excel system you do the following:<\/p>\n
- \n
- Add the System to your project<\/li>\n
- Make a spreadsheet and use range names to identify parameters<\/li>\n
- Attach an Excel spreadsheet<\/li>\n
- Edit the system and tell the program which parameters are input and which are output<\/li>\n
- Go into the parameter manager and hook up any derived parameters you want to pass to Excel and use any of the Excel parameters with other parameters as needed<\/li>\n
- Tell ANSYS to run a VB macro (if you want)<\/li>\n
- Update your project or Design Points<\/li>\n<\/ol>\n
We will go through the process in detail but first, a few things you should know:<\/p>\n
- \n
- The system kind of looks and feels like the parameter manager in Workbench, but it is not.\u00a0 You have to think of Excel as a \u201csolver\u201d<\/strong><\/em> that feeds parameters from and to the parameter manager.\n
- \n
- I struggled with this because I thought of output parameters as values calculated by Workbench and input parameters as ones that come from Excel, but the opposite is true.<\/li>\n
- Excel Input Parameter: A value calculated in Workbench parameter manager<\/li>\n
- Excel Output Parameter: A value calculated in Excel<\/li>\n
- You need to get your head around this or you will get stuck like I did.\u00a0 The example should help.<\/li>\n<\/ul>\n<\/li>\n
- Parameters that come from DesignModeler are dimensionless<\/strong><\/em> in the parameter manager.<\/li>\n<\/ul>\n
- \n
- This one really held me up for a while.\u00a0 If you assign a parameter from Excel that has a unit to drive your geometry in design modeler, you get an error.<\/li>\n
- The solution is to make sure that you DO NOT use units on Excel parameters that you get or pass to DesingModeler<\/li>\n
- When you attach the Excel file to your Excel system on the project page Workbench copies the Excel file to your project<\/strong><\/em> and buries it in dpall\\XLS.<\/li>\n<\/ul>\n
- \n
- You will get burned by this if you go to your original excel file, edit it, then try and update your project.\u00a0 Your changes will not show up.\u00a0 That is because it is not linked to the original file, it is linked to a copy stored in that XLS directory.<\/li>\n
- Once you have linked a file you should exit Excel then open the file by RMB on the Excel system and choose\u00a0 \u201cOpen File in Excel\u201d (see below for more on this whole process)<\/li>\n
- I recommend that you start buy making your Excel file, save it with the name you want in the C:\\Temp directory, attach that file, close Excel, then open from Workbench.<\/li>\n
- Now you have a file to add your stuff to and you don\u2019t have to worry about having an earlier version lurking around.<\/li>\n
- An important side effect of this is if you delete your system, it deletes your Excel file!\u00a0 So make sure you make a copy or do a save as before you remove the Excel system<\/li>\n
- To get changes in Excel to show up in your project, you need to save the file AND refresh\/reload<\/strong>.<\/em><\/li>\n<\/ul>\n
- \n
- Making a change to he Excel file will put the system out of date.\u00a0 A refresh on the project page or a reload on the \u201cEdit Configuration\u201d page will update things.<\/li>\n
- The parameter names in Excel are case sensitive<\/strong>.<\/em>\u00a0 So whatever your prefix is in the system properties (WB_ by default) you need to have the same in your Excel spreadsheet for range names.<\/li>\n
- To get a full update, including running any macros and doing any calculation, you have to update the system.<\/strong><\/em>\u00a0\u00a0 This is kind of obvious, but I kept forgetting to do it.<\/li>\n
- Your Excel file will not update if you use RSM<\/strong>.\u00a0 Make sure your default for updating your project is to run local and, that if you are using design points, you set that update to run in the foreground.<\/li>\n<\/ul>\n
- \n
- The easiest way to check and change this is to click on the parameter bar and view its properties.\u00a0 Under Design Point Update Process set Update Option to Run in Foreground.<\/li>\n
- If you want to have your Excel file define both input and output parameters for the same ANSYS simulation, workbench sees that as\u00a0 a \u201ccyclic dependency<\/em>\u201d<\/strong> and will not let you do it.<\/li>\n<\/ul>\n
- \n
- Although annoying at first glance, it kind of makes sense.\u00a0 If you feed a value to Excel and then Excel calculates a new value that effects your ANSYS model, you need to update the ANSYS model, which will change the value that gets passed into Excel, which will change the value that gets passed out which changes your ANSYS model, which\u2026 and so it goes in a loop. This is considered a bad thing.<\/li>\n
- This goes back to the fact that Excel should be used as a solver, not as\u00a0 ‘\u201ddriver\u201d of you simulation.<\/li>\n
- If you do want to drive your analysis from Excel, you\u2019ll need to do some scripting. We\u2019ll cover that in a future article.<\/li>\n<\/ul>\n
The Process<\/h2>\n
I started this article with a really cool valve demo model. Then found that it was just too slow and a pain to work with for showing how the Excel system works.\u00a0 So I went back to my second favorite type of model, a simple \u201ctower of test.\u201d\u00a0 (my favorite is a FPWHII \u2013 flat plate with a\u00a0 hole in it).\u00a0 You can download the project here<\/a>.<\/p>\n
Add the System to Your Project<\/h2>\n
Like every other system in Workbench, you simply drag from the toolbox to the Project Schematic.\u00a0 Notice how the green \u201cdrop zones\u201d are all empty spaces.\u00a0 You can\u2019t drop it on an existing cell in a system because there is no dependency between other systems and an Excel system.\u00a0 The Excel system is connected through parameters, which we will see in a bit.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_120.png\" "\\"image\\"")
<\/a><\/p>\nOnce you have dropped it onto the schematic, click on the Top cell (C1 in this case) and check out the properties (RMB Properties if the window is not already open).\u00a0 From the properties you can see the system ID (XLS) and you can specify an Analysis Type.\u00a0 You can leave it blank or type in something like \u201cHome Grown Optimization.\u201d<\/p>\n
Then click on the Analysis cell (C2 in this case) and look at the properties.\u00a0 They are shown here:<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_121.png\" "\\"image\\"")
<\/a><\/p>\nOne key thing to note is that the directory where the Excel file will be copied is shown.\u00a0 I did this once already on this project so it made a XLS-1 directory. If I did it again, I\u2019d see XLS-2, etc\u2026\u00a0\u00a0 In fact, by the time I got done with this article and trying all sorts of things, it ended up in XLS-8.<\/p>\n
The most important option under Setup is the \u201cParameter Key\u201d\u00a0\u00a0 Any Excel named range that begins with this string will get read into the parameter manager.\u00a0 If you make it blank, all the named ranges will come in.<\/p>\n
Make a Spreadsheet and Use Range Names to Identify Parameters<\/h2>\n
Now you need to create your spreadsheet.\u00a0 You need to plan ahead here a bit.\u00a0 Figure out what parameters you need Excel to get from your models and what parameters you want to send back.\u00a0 Come up with good names because that is what gets passed to Workbench.<\/p>\n
What happens when you attach a file is that Workbench goes to the Excel sheet and steps through all the named ranges in the file.\u00a0 If it finds one with a name that starts with the filter value, it grabs the first value in the range as the parameter value and then grabs the second as the units.\u00a0 If your range is bigger, it just ignores the rest.<\/p>\n
So this tells us that we need to create a range that has at least one cell, or two if units are important. For our simple example we will be calculating costs\u00a0 and outputting that using the input Volume, Length and Width. There is a formula in the cost cell that multiples those values times pre-set costs per unit volume, length and width and sums them up to get a cost.<\/p>\n
So the laziest thing you can do is select a cell and name it.<\/p>\n
But it will help you and others if you actually make a table that has a descriptive name, the parameter name (WB_ should be your default), the value for that parameter, and the units, if any.\u00a0 Note that for an input parameter you can just set the value to zero to get started.\u00a0 Here is what the tables look like for our example:<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_122.png\" "\\"image\\"")
<\/a><\/p>\nTo create a range you select the value and units for a given parameter, hold down the Right-Mouse-Button (RMB), and Select \u201cDefine Range\u201d<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_123.png\" "\\"image\\"")
<\/a><\/p>\nA cool thing that Excel does is to use the value just to the left of the range as the default name of the range.\u00a0 So by creating the table you save yourself some typing.\u00a0 Or, if you don\u2019t use a table, just type in the name you want .<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_124.png\" "\\"image\\"")
<\/a><\/p>\nNow just click OK and you have a named range.\u00a0 You can repeat this for each range, or you can get fancy and use the fact that your data is in a table, with the parameter name to the left, to quickly generate all the ranges at once.<\/p>\n
To do this, select the WB Param, value and unit columns. Then go to \u2013>Formulas \u2013> Defined Names \u2013> Create from Selection.\u00a0 When the dialog box pops up make sure only \u201cLeft column\u201d is checked.\u00a0 Click OK.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_125.png\" "\\"image\\"")
<\/a><\/p>\nIn one fell swoop you created all your named ranges.\u00a0 To see, edit, and delete ranges, regardless of how they were created, go to Formulas \u2013> Defined Names \u2013> Name Manager.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_126.png\" "\\"image\\"")
<\/a><\/p>\nTake some time to look at this and understand it. When you are debugging and fixing stuff, you will use this window.<\/p>\n
Now you have an Excel file that Workbench will like!\u00a0 Time to attach\u00a0 it. Save it (I recommend to save to temp so you don\u2019t get it confused with the copy that Workbench will make).<\/p>\n
Attach an Excel Spreadsheet<\/h2>\n
This is the easiest step. Simply RMB on the Analysis cell in the system and browse for the file.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_127.png\" "\\"image\\"")
<\/a>Now your Analysis cell has a lightning bolt, update to have it read the file and find parameters.\u00a0 If you have your parameters set up wrong, such that you don\u2019t have any named ranges with the specified prefix, it will generate an error but will still attach the file.<\/p>\nNOTE:\u00a0 If you get some weird errors\u00a0 \u201cUnexpected error\u2026\u201d and \u201cException from HRESULT:\u2026\u201d when updating your Excel system, check your Excel file.\u00a0 Odds are you have an open dialog box or the file is somehow locked. The error generates because Workbench can\u2019t get Excel to talk to it.<\/p>\n
Edit the System and Tell the Program Which Parameters are Input and Which are Output<\/h2>\n
Although you have a green check mark, you will notice that your system is still not connected to your parameters, and therefor it is not connected to the rest of your model.\u00a0 The way to fix this is to RMB->Edit Configuration. Double-clicking on Analysis also does the same thing.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_128.png\" "\\"image\\"")
<\/a><\/p>\nThis puts you in Outline Mode.\u00a0 You should be familiar with this mode from the Parameter Manager or Engineering Data.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_129.png\" "\\"image\\"")
<\/a><\/p>\nTake some time to explore this outline.\u00a0 Notice the setup cell, where you have access to the system properties.\u00a0 Then it\u2019s child, the Excel file.\u00a0 Click on it to the properties for the file connection.\u00a0 Under that is the important stuff, the parameters.<\/p>\n
If you did everything correctly, you will see all of your parameters in alphabetical order.\u00a0 If you click on one, you will see the properties.\u00a0 Here they are for the cost value:<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_130.png\" "\\"image\\"")
<\/a><\/p>\nIt shows the range, the value and units (C column) and the Quantity name.\u00a0 Workbench guesses by units.\u00a0 So PSI comes in as pressure by default.\u00a0 If it is a stress, you need to change it here.<\/p>\n
But your main task right now is to tell Workbench which of these parameters you want passed to the parameter manager, and what type of parameter, input or output, they are.\u00a0 Here is where I get screwed up.\u00a0 Because an input parameter in the parameter manager is an output parameter here.\u00a0 Remember, the Excel system is a solver that takes in parameters from the parameter manager and send back values to drive your models.\u00a0 So in our example, all the dimensions and the volume are passed from the parameter manager TO excel, so they are input.\u00a0 The cost is passed from Excel to the Parameter manager so it is output.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_131.png\" "\\"image\\"")
<\/a><\/p>\nNow you have hooked up your Excel system. Click on the \u201cReturn to Project\u201d at the top of the window and you will go back to the project schematic and see that a Parameters cell has been added to the system and it has been attached to the parameter bar.<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_132.png\" "\\"image\\"")
<\/a><\/p>\nGo Into the Parameter Manager and Hook Things Up<\/h2>\n
Although the Geometry and Mechanical systems are connected through the parameters to the Excel system, no relationships exist.\u00a0 We need to assign some values to our Excel parameters.<\/p>\n
This is what our test model looks like before we do this:<\/p>\n
![\"image\"](\"\/wp-content\/uploads\/oldblog\/image_thumb_133.png\" "\\"image\\"")
<\/a><\/p>\nOur goal is to have the parameters in the first column below to drive those in the second:<\/p>\n
\n
- To get a full update, including running any macros and doing any calculation, you have to update the system.<\/strong><\/em>\u00a0\u00a0 This is kind of obvious, but I kept forgetting to do it.<\/li>\n
- The system kind of looks and feels like the parameter manager in Workbench, but it is not.\u00a0 You have to think of Excel as a \u201csolver\u201d<\/strong><\/em> that feeds parameters from and to the parameter manager.\n