×

General Software

Alibre Options: Harnessing Excel Spreadsheets to Drive Your Mechanical Designs

14 Jun, 2005 By: Michael Todd Cadalyst

---

Use Excel spreadsheets to organize and tie your designs to common dimensions

Alibre Design can derive dimensions for parts and assemblies directly from an Excel Spreadsheet. With a provided add-in to Excel, you can tie dimensions in your Alibre Design files to any cell in an Excel spreadsheet. You also can make changes to the Excel spreadsheet, open the parts and they will update accordingly. This way you can layout and organize all your dimensions in spreadsheets, tie multiple designs to common dimensions and use the power of Excel (2000 or later) to do calculations.

1. Install the Alibre Design add-in for Excel.
  a. Open an Excel spreadsheet and select Tools / Add-Ins.
  b. In the Add-Ins dialog box browse to the program directory for Alibre Design (generally C:\PROGRAM FILES\ALIBRE DESIGN), select ALIBRE DESIGN ADD-IN.XLA and click OK. Alibre Design Add-In appears in the Add-in list.
  c. Make sure Alibre Design Add-in is checked and then click OK.

2. Select Tools / Options / General Tab and make sure "R1C1 reference style" is not checked (figure 1).

3. Create your Excel spreadsheet with any numbers that needed to drive your design (figure 2). Do not include the units in the same cell as the value. Save the spreadsheet.

4. Start the Alibre Design software.

5. In Excel, select Tools / Alibre Design Add-in / Control Parameters. The Control Parameters dialog box opens (figure 3).

6. Select the Alibre Design file to use with your Excel spreadsheet. You can select a file that is either in an active session (i.e., open workspace), saved in a repository or saved in the Windows file system.

• If the part is in the Windows file system, select File System and browse to the part or assembly file; then select Open.
• If the part is in a repository, select the button. The Browse for Repository Item dialog box opens. The top drop-down list is the list of available repositories, the left pane is the folder tree of the selected repository and the right pane is the list of items in the selected folder.

Note: The filenames do not have file extensions. So, if you have parts, assemblies or drawings with the same name and only the extension is different, you'll have some trial and error. One safe thing to do is make sure all files have different names. Note: When you select a part from a repository or file system, the part opens in a part workspace. Do not close the design window until you finish linking to the Excel spreadsheet.

• If the part or assembly you want to use with the Excel spreadsheet is open, choose Active Sessions and select the file from the dialog box.
• The Control Parameters dialog box now contains the current dimension values used in the design (figure 5).

Note: The values in the Control Parameters dialog box are based on model units (not display units), which most often will be centimeters, but could be any of the units supported by Alibre Design. You cannot change the model units, but you can override them per parameter, using the Control Parameters dialog box.

7. Pick the parameter that you want to control with the Excel spreadsheet. In figure 5, D2 is selected.

• Select Cell Reference and choose which cell controls the dimension.
• Select Units and choose the correct units for the value in the Excel spreadsheet. This may be different than the units listed in the Parameters list.
• Click Modify.

Note: You can also name the cell that contains the dimension value and type the cell name in the Cell Reference field. Now, if the cell moves on the spreadsheet, it stays linked to the parameter. The grid position is still shown in the Cell column of the parameter list (figure 6).

The cell location is now referenced for that particular parameter and the Current Value matches the value in the spreadsheet.

8. Click Close; then save the part in Alibre Design.

9. In the Alibre Design part workspace, open the Equation Editor dialog box (figure 7). Note that the equation is now controlled by a file and the current value is listed. Close the dialog box.

10. Close the part workspace.

11. Now let's try changing the dimension controlled by the spreadsheet.
  a. In the spreadsheet, change the value from 1.25 to 1.5 (figure 8).

  b. Select Tools / Alibre Design Add-in / Control Parameters and open the file linked to the spreadsheet.

Note that the new value is listed in the New Value column. "---" means the value is unchanged. "???" means the value is not valid, such as a division by zero.

  c. Select the changed parameter and click Modify. The part is now modified.
  d. Open the part to see the change. Save the part.

12. You can also modify the information in the Control Parameters dialog box. You can change the Name, Cell Reference and Units of any of the parameters. In figure 9, the name of the parameter was changed from "D2" to "Diameter." Click Modify to commit the change or Reset to discard the change. Also, if you select another row without clicking Modify, the changes are discarded.

Note: If you change the name of a parameter, you must update any references to that name in calculations for other parameters in Excel spreadsheets or the Equation Editor in Alibre Design.

13. You can also add a parameter with the Control Parameters dialog box.
  a. If a row is already selected, click Reset to enable the Add button.
  b. Enter a Name, Cell Reference, Type (Distance, Angle, Count or Scale) and Units (figure 9).
  c. Click Add. The new parameter is listed and will now appear in the Equation Editor of the part; it may be used for future reference.

14. If you need to move the Excel spreadsheet, perhaps to a repository to streamline data management, you must reestablish the link between the spreadsheet and the part using one of two methods:
  a. Open the spreadsheet from the new location, and follow the steps above to reestablish the cell references on the new spreadsheet.
  b. Use the Link functionality in the Equation Editor of the part (figure 10).

Click the Link button; then browse to the moved or new Excel spreadsheet that you want to use to control the parameter. The Excel spreadsheet may be in either the file system or one of your repositories.

Excel spreadsheets can be a powerful tool for your 3D design projects. Now you can have all of your dimensions organized in spreadsheets, tie multiple designs to common dimensions and do advanced calculations.

Next month we'll explore using Exploded Views created in the 3D workspace to create 2D drawing views. Until then, look for me as the Alibre Assistant online in Alibre Design.