Autodesk Inventor Services & Support

By Bill Fane

The power of parametric design software lies in the fact that dimensions don’t just numerically represent the size of an object but actually control the size. If you change the number, then the object’s size changes to match. You can also establish relationships between dimensions so that changing one dimension changes not only its object but other objects as well. If you make the part bigger, then it can also automatically get thicker.

Autodesk Inventor® software has carried this capability to a fine art. Its associativity functionality makes it simple to establish relationships between different parts in an assembly. As Neil Munro showed in his tutorial, you can project sketch geometry from one part to another, and Autodesk Inventor maintains the relationships between the sketch objects in the two parts. If you change the size of the box, then the lid automatically adjusts to fit.

Connections: Linking Sketch Dimensions to a Spreadsheet

The parametric and associative functions of Autodesk Inventor are great as far as they go, but at times we need more. The good news is that Autodesk Inventor has more. In this month’s tutorial you learn how and why to use a Microsoft® Excel spreadsheet to control the relationships between features in a part and between parts in an assembly.

Let’s begin with an exercise that shows how easy it is to link an Autodesk Inventor part to an Excel spreadsheet.

Note: To complete this tutorial you must have Microsoft Excel and Autodesk Inventor® 5.3 or later installed on your computer.

1. Create a folder called c:\BF-04.

2. Download the sample file.

3. Unzip the contents of the file into the folder c:\BF-04.

4. Start Microsoft Excel and open the file c:\BF-04\BF-04-01.xls. It should look like Figure 1.

Sample File (zip - 535Kb)

5. Take a brief look at the contents. We will study it later in more detail.

6. Start Autodesk Inventor software, and select the project file BF-04 from the folder c:\BF-04.

7. Open the part file BF-04-01.ipt in the project folder. It should look like Figure 2.

8. From the Tools menu, choose Parameters, or choose the Parameters button on the Standard toolbar. The Parameters dialog box displays, as shown in Figure 3.

9. Click the Link button to display an Open dialog box. If you have activated the supplied project file, then you should automatically see the correct folder, c:\BF-04. The Open dialog box should contain the single spreadsheet file BF-04-01.xls.

10. Double-click the spreadsheet file name. The Parameters dialog box displays.

11. The Parameters dialog box now looks like Figure 4. Observe how the additions at the bottom of the dialog box bear a remarkable similarity to the spreadsheet in Figure 1!

12. To close the Parameters dialog box, click Done.

Note: Clicking the X in the upper-right corner of the dialog box also closes it, but the link has already been made. If you connect to the wrong sheet, or change your mind, you must click Undo before you close the dialog box or before you select another spreadsheet.

That’s all there is to it! With a few simple clicks you have linked the cells in an Excel spreadsheet to the parameter names in the Autodesk Inventor part file. Now let’s use these new parameters in our part model.

1. Double-click the 2.823 dimension. The Edit Dimension dialog box displays.

2. Click the right-arrow symbol at the right end of the value window, and then choose List Parameters. A Parameters dialog box displays, listing the three named parameters from the spreadsheet.

3. In the parameter list, click Length. The Edit Dimension dialog box displays.

4. To accept the change, click the green check mark. The sketch updates to reflect the value from the spreadsheet.

5. Double-click the 1.756 dimension.

6. Repeat steps 2 through 4, but this time select the Width parameter.

The two dimensions are now linked to the cells in the spreadsheet. Let’s see what happens when you change the parameters.

1. If you don’t have the spreadsheet open in Excel, go to the Autodesk Inventor browser, expand the 3rd Party entry, and double-click the spreadsheet name. Excel launches, and the spreadsheet opens.

2. Double-click cell B2, and change its value from 10 to 14.

3. Double-click cell B3, and change its value from 5 to 8.

4. From the File menu, choose Save, or on the Standard toolbar, choose Save.

5. Return to Autodesk Inventor, and on the Standard toolbar, choose Update. Watch your sketch update to reflect the new spreadsheet values!

The Part Thickens

Now that you have the sketch dimensions linked to the spreadsheet, it is time to extrude the sketch into a solid. In this exercise you see how you can use linked parameters in places other than sketch dimensions.

1. On the Features tool panel, click Extrude. The Extrude dialog box displays.

2. At the right side of the window, click the right-arrow symbol that shows the distance value, and then choose List Parameters.

3. In the Parameters dialog box that displays, click Height. The Parameters dialog box closes, and the Extrude dialog box displays.

4. Click OK, and the sketch extrudes into a solid brick whose height is determined by the appropriate spreadsheet cell.

This exercise merely hints at the power of Autodesk Inventor software. When you use named parameters, including parameters linked to a spreadsheet, you are not restricted to using them in sketch dimensions. In fact, you can use named parameters, linked parameters, and formula relationships anyplace that requires a value. This includes extrusion distances, revolution angles, draft angles, assembly constraint offsets, and pattern distances and counts.

Before we move on, do a little more editing in the spreadsheet. Change all three values, including the depth. Make sure the Width value is greater than two. Save the spreadsheet. Update the Autodesk Inventor part, and watch as the part changes to match the spreadsheet.

All Together Now

We are about to begin investigating one of the more powerful aspects of linked spreadsheets. The good news is that it requires no additional work.

1. Open the assembly file BF-04-02.iam. Click Yes to update the assembly.

2. Open the 2D drawing file BF-04-02.idw. It should look something like Figure 5. I say “something like” because it probably does not show the same dimension values. Instead, the red (upper) part displays the latest values that you saved in your spreadsheet.

Observe how the green (lower) part is two units longer and two units narrower than the red part (which is why I said to keep the Width variable larger than two), and it is twice as thick. Named variables can be used in formulas.

3. Change the values in the spreadsheet, and save the changes.

4. Update the 3D assembly model.

5. Display the 2D assembly drawing, and observe how both parts have updated to reflect the new spreadsheet values.

So how did I do it? I simply created two parts, linked them to the same spreadsheet, and then built the assembly. That’s it. The assembly did not require any other actions.

Now that you have seen how easy it is to link parts and assemblies to a spreadsheet, let’s look at the format of the data in the spreadsheet.

The basic format requires four cells per variable, in the following order:

1. Variable name
2. Current value
3. Units (optional if they match the file units)
4. A comment (also optional)

All variable names must be in adjacent cells. Autodesk Inventor stops linking as soon as it encounters the first empty cell in the list of variables.

The variable names are not limited to a column arrangement, like our sample. A row arrangement, as shown in Figure 6, works just as well.

Figure 6 also indicates that the data need not start at cell A1. The file dialog box that you use to select the spreadsheet also has a window for selecting the location of the first cell. In Figure 6 it would be F7.

The third cell (row 9 in Figure 6) holds units. It can be blank if it matches the part file units, which it normally does for distances. If it is going to hold a quantity, such as the number of rows in a pattern, then the units often need to be “UL.”

Now for the one point that makes spreadsheets so powerful: the value cell is not limited to holding a simple numeric value. It can contain any Excel functions, formulas, lookup tables, and so on as long as they resolve to a numeric value.

For example, as a flange gets larger it could have more bolt holes, and the holes could get larger, but only in the discrete increments of standard sizes.

Tips, Tricks, and Traps

Now that we have seen how easy it is to set up and link a spreadsheet to Inventor, let’s take a closer look at some of the details.

• Variable names must be unique. If the spreadsheet contains a variable name that matches a user-defined name in the part, then the software will ignore the variable in the spreadsheet.
• You can link more than one spreadsheet to the same part file, but, again, the variable names must be unique. If there are duplicates, the first one in takes precedence.
• More than one part can reference the same spreadsheet. That is how you can establish relationships between different parts in an assembly.
• Variable names are case sensitive. Width is different from width, which is different from wiDth. Case can be an issue if you choose to type the variable name rather than selecting it from the context-sensitive menu.
• You can delete the link to a spreadsheet by right-clicking on its name under 3rd Party in the browser. When you do, variables get frozen at their current values. The only way to reestablish the link is to go to the Parameters dialog box, delete each variable, and then relink to the spreadsheet.
• If you embed rather than link, then the spreadsheet gets absorbed into the part and affects only the host part.
• If you send a part or an assembly file to someone else you must also send the spreadsheet. The Pack and Go feature handles this automatically.

Spreadsheets Versus Adaptivity or Shared Sketches

Why should you use spreadsheets instead of adaptivity or shared sketches? Here are just some of the reasons:

• Once it is set up, a spreadsheet can be a lot faster to use. A simple change to a spreadsheet cell makes the necessary changes to the drawing, without the user having to wade through the drawing itself.
• Adaptivity affects only one feature in the part, and shared sketches are limited to two dimensions. As these exercises have shown, a spreadsheet can control all three dimensions.
• Spreadsheets can contain more intelligence and decision-making capabilities.
• Spreadsheets act as a bridge between parts in an assembly. For example, changing the Bore and Stroke in a single spreadsheet could affect hundreds of dimensions in dozens of parts. Making such changes can be so simple that even the Order Entry department can make them.

Conclusion

As these exercises have shown, linking Excel spreadsheets to Autodesk Inventor parts and assemblies is simple. Once you master the details, this capability greatly expands the software’s power and versatility.