Autodesk Mechanical Desktop: Your Table Is Ready By Bill Fane
At first, the thought of connecting an Excel spreadsheet to a Mechanical Desktop? model sounds a little scary, but as you will see in this tutorial it is actually a very simple task to accomplish. As you know, Mechanical Desktop? software is a parametric modeler. If you change the numeric value of a dimension, everything resizes itself to match. This gives us two major benefits:
You can easily tweak and fine-tune a design, and play "what-if" scenarios, until the design is optimized. You can easily develop a family of similar parts. Making Connections Developing a family of similar parts becomes particularly powerful when you learn how to use an Excel spreadsheet to drive the configuration of a Mechanical Desktop part model.
Figure 1: A simple bracket.
Connecting an Excel spreadsheet to a Mechanical Desktop part is a simple four-step operation. We'll use the simple bracket in Figure 1 below as our working model. I hope it looks remarkably like the one that appears in last month's Suppress Your Best Features article. That's because we'll be talking about feature suppression a little bit later.
First, create named variables.
1. Start the AMVARS by:
a. Entering it at the Command: prompt, or b. Click Part > Design Variables, or c. Click Design Variables on the 2D Constraints toolbar, or d. Right-click on the part name at the top of the browser, then click Design Variables in the shortcut menu.
Figure 4: Edit the dimensions to use the named variables instead of numbers.
View Larger Image Figure 2: The Design Variables dialog box.
2. This brings up the Design Variables dialog box (see Figure 2).
a. In the Design Variables dialog box, click New to bring up the New Part Variable dialog box, and fill in the boxes as shown in Figure 3. The comment is optional.
b. Click OK to return to the Design Variables dialog box, where your data now appears in the main window. c. Repeat steps a) and b) two more times so the Design Variables dialog box ends up with the following three entries: Height 5 Width 4 Depth 3
d. Click OK to exit the Design variables dialog box.
Second, use the variables within dimensions.
Figure 4 below shows the paper space layout of two 2D drawing views of a bracket (I have suppressed the dimensions that do not apply to this tutorial).
Figure 5: This spreadsheet will drive the part.
There are at least two ways of changing the dimensions to include your new variables: a. In Layout mode, use AMMODDIM (click Annotate > Edit Dimensions > Edit Dimension).
Then click on a desired dimension. Enter the name of the variable, then press Enter. Continue selecting dimensions and entering variable names. When you are finished, press Enter without selecting a dimension. Click the Update Part button at the bottom of the browser to have your part and layout views update to reflect the values you entered in the Design Variables dialog box. Or:
b. In model mode, use AMEDITFEAT:
Double-click the feature in the browser. Click OK to close the Extrusion dialog box. Click on a desired dimension. Enter the name of the variable, then press Enter. Continue selecting dimensions and entering variable names. When you are finished, press Enter without selecting a dimension. The part will automatically update to reflect the values you entered in the Design Variables dialog box. Third, create the spreadsheet.
1. Create a standard Microsoft Excel spreadsheet containing four rows and four columns, containing the values shown in Figure 5 below. Note that columns B through D each contain a word in the first row that matches a variable name from the drawing.
2. Save it, using a suitable name and location.
View Larger Image Figure 6: The Table Driven Setup dialog box.
Figure 7: The browser, showing the linked table.
Finally, link the spreadsheet to the part.
1. Start the AMVARS command again as described above to produce the Design Variables dialog box of
2. In the lower-left corner of the Design Variables dialog box, click Setup... in the Table Driven box. This brings forth the Table Driven Setup dialog box (see Figure 6).
3. In the upper-right corner of the Table Driven Setup dialog box, click Link... to bring up a standard Windows file dialog box. Browse as necessary to find your spreadsheet table and open it. 4. Click OK in the Table Driven Setup dialog box to return to the Design Variables dialog box. 5. Click OK again to close the Design Variables dialog box.
Note: All your design variables are now grayed and cannot be edited. The linked table will appear in your browser (see Figure 7). The browser now has an entry near the top that indicates the presence of the linked spreadsheet.
Figure 8: The spreadsheet will now control feature suppression.
Row, Row, Row Your Part Now we come to the magic. Referring again to Figure 7, notice how the expanded spreadsheet item in the browser has three symbols, each with a word beside it. By an amazing coincidence, these are the three words from column A of the spreadsheet, and each of them identifies a row from the table. Double-click on any one of these row identifiers in the browser and watch open-jawed as Mechanical Desktop automatically goes through the following steps:
1. It accesses the spreadsheet table on disk. 2. It finds the row corresponding to the one you selected. 3. It reads each of the column values from that row and feeds them back to the matching named variables in your drawing. 4. It passes the new variable values over to the dimensions that use them. 5. Everything updates, both in model mode and in the 2D layouts. Pretty cool, huh?
Suppress That Rib Did you notice the rib in Figure 1? You may remember that, last month, the smallest bracket did not need a rib.
Well, we still don't need it. Suppressing the rib requires just a few simple steps. 1. Edit the spreadsheet as shown below in Figure 8. Make sure you leave a blank column E as shown so Mechanical Desktop will know that this is the end of the variable names and the start of feature names. Note that the word in the first row of column F matches the name of a feature in the browser. Save your changes. 2. Start the AMVARS command again as described above to produce the Design Variables dialog box shown previously in Figure 2. 3. In the lower-left corner of the Design Variables dialog box, click "Setup..." in the Table Driven dialog box. This brings forth the Table Driven Setup dialog box previously seen in Figure 6. 4. In the Type box, click the Both radio button. 5. Click the Update Link button, then click OK twice to exit the Table Driven Setup dialog and the Design Variables dialog box.
This single table now not only controls the sizes of the three variables, but also the suppression of the rib feature. I went from Figure 9a through 9c in a few seconds with just a couple of double-clicks. Now that is real magic!
Figures 9a, b, c: A couple of double-clicks took the part from a through c.
Helpful Tips Now that you how to link a spreadsheet to a Mechanical Desktop part, here are a few helpful tips.
Microsoft Excel is the only spreadsheet program that Mechanical Desktop supports. The spreadsheet does not have to have an entry for every variable in the part. You might want it to control just a few of them. (Conversely, any extra columns in the spreadsheet will be ignored if there isn't a matching variable or feature name in the part.) The spreadsheet column names do not need to be in the same sequence as the variable names in the Design Variables dialog box. Any number of drawings can be linked to the same spreadsheet, and can use the same variable names. A single spreadsheet can be used to drive several or all of the parts that go into an assembly. The spreadsheet cells do not have to contain simple numeric values. They can include formulas, decisions, references to other cells, and look-up tables. Mechanical Desktop uses the final numeric resultant. The spreadsheet cells that control feature suppression do not have to contain the single letter S. Once again, they can be decisions, cell references, or look-up tables that resolve (suppress) or blank (unsuppress) anything. If you make changes to the spreadsheet and save it, then right-click on the spreadsheet name in the browser and select Update from the shortcut menu. Everything will update to the new values. You do not have to create a new spreadsheet specifically for Mechanical Desktop. If you have an existing spreadsheet, the Design Variables dialog box includes options to select the starting cell to be something other than A1 and to have the version names read across rather than down. You can even have a one-row spreadsheet that drives a part. The spreadsheet values can be the result of complex calculations within the sheet. Simply enter a few "super parameters" within spreadsheet cells, let it do your stress/pressure/load calculations, and the part updates. Conclusion Table-driven parts are easy to create and use, especially when you realize how powerful they can be. As you have seen, you can easily amaze your friends and improve your productivity with just a few minutes work.
Be sure to come back next month when we explore the use of variables and spreadsheets within assembly models.