For engineers who already know the math—but still lose projects. For the last few years, I’ve been sharing technical guides here on Mechanical Design Handbook —how to size a motor, how to calculate fits, and (as you recently read) how to choose between timing belts and ball screws. But after 25 years in industrial automation, I realized something uncomfortable: Projects rarely fail because the math was wrong. They fail because: The client changed the scope three times in one week. A critical vendor lied about a shipping date (and no one verified it). The installation technician couldn’t fit a wrench into the gap we designed. University taught us the physics. It didn’t teach us the reality. That gap is why I wrote my new book, The Sheet Mechanic . This is not a textbook. It is a field manual for the messy, political, and chaotic space between the CAD model and the factory floor. It captures the systems I’ve used to survive industrial projec...
To link Solid Edge variables to Microsoft Excel, we can do so by entering a formula in the variables table or by using VBA code.
Advertisement
In this post, I would like to show how easy it is to make it using the variables table. You can use the Variable Table to define and edit functional relationships between the dimensions of a design in a familiar spreadsheet format.
The Variable Table is accessed with the Variables command on the Tools menu (or the Tools tab > Variables group in modern versions). There are two types of variables: dimensions displayed in the design and variables you create directly in the Variable Table.
- Dimension variables directly control elements of a design.
- Variables that you create cannot directly control the design but can drive dimension variables. A user variable must be set equal to a value or mathematical expression; for example, PI = 3.14159.
Let's begin...
1. Create the Mechanism Sketch
Open Solid Edge and make a sketch as shown below.
Figure 1: The fully constrained mechanism sketch ready for automation.
The mechanism in this example consists of a cam, lever, pull rod, and four-bar linkage. What we have to do is to make sure that when the dimension of the cam angle is changed, the entire mechanism is changed accordingly. You can simply check this by clicking at the dimension of the cam angle and rotating your mouse wheel to see if the entire mechanism is moving or not.
2. Set the Name of Variables
When you place the dimensions for a design, variables for these dimensions are placed into the variable table automatically. If the Variable Table is open, any dimension that is placed by you or the software will display in the Variable Table after the dimension is placed.
Working with the variable table open allows you to change the dimension's name that is generated by the software to a more logical name as you work. When you rename variables, the variable names should contain only letters, numbers, and the underscore character. You should not use punctuation characters.
In Solid Edge: Select Tools > Variables > Variables.
Click at the dimension you want to change the name. For this case, select the dimension of the cam angle and change "Name" to cam_angle. Select "Expose" to show this variable name to other applications as shown below.
Advertisement
Figure 2: Exposing the variable allows external applications to read and write to it.
3. Link Variable in Solid Edge to Microsoft Excel
You can use Microsoft Excel or other spreadsheet software to link Solid Edge variables to a spreadsheet. Before you can link variables to a spreadsheet, you must first create the variables you want in the Solid Edge document.
To successfully edit the linked Solid Edge variables from the spreadsheet later, you must open the Solid Edge and spreadsheet documents in a specific order:
- You can open the spreadsheet document first, then open the linked Solid Edge document.
- You can open the Solid Edge document first, then click the Edit Links command on the shortcut menu when a linked formula is selected within the variable table. You can then use the Open Source option on the Links dialog box to open the spreadsheet document.
Create a new Microsoft Excel workbook and save it. In this example, the file is named ex-solid-edge1.xls.
Figure 3: The simple Excel interface that will drive the complex CAD geometry.
In Solid Edge: Select Tools > Variables. Select the variable named cam_angle as you created in the previous step. Enter the following formula into the "Formula" column:
@'E:\Mechanical-Design-Handbook\ex-solid-edge1.xls'!'Sheet1!R2C2'
You can see that to link to another excel workbook, just put @ at the first position then follows by the path and file name of excel file, sheet name and cell.
R2C2 means Row 2 and Column 2, which corresponds to cell B2.
Now the Solid Edge variable is linked and controlled by the cell value in Excel. If you change the value in cell B2, you will see the movement of the cam mechanism.
Watch the result in the following video clip:
Continue to the next tutorial:
🚀 Motion Simulation in Solid Edge using sketches together with Microsoft Excel VBA Programming Part 3
Comments