In the PARAMETRIC BOOKCASE tutorial, we covered how to create equations to use the parametric ability of Alibre to our advantage. In this tutorial we will link those separate parts--the back, the side and the shelf--to a common spreadsheet in order to easily change the overall dimensions of our bookcase.

This will take what would be a cumbersome model to use, and make it into a cohesive model that will allow us to crank out bookcase designs with amazing ease. Once you have completed this tutorial, you can take the bookcase model and make it your own by changing the construction to suit your construction methods. You will then have a re-usable template for designing bookcases.

You need to have a copy of the Microsoft Excel spreadsheet program on your computer. We use Excel versions 2000 and 2003 with Alibre, and the documentation states that Excel XP & 2007 will also work.. You need to install the Alibre Add-In for Excel using the information on page 220 of the Alibre Design 9.2 Users Guide.

You can drive multiple Parts and Assemblies in Alibre with Excel, as the information that links Alibre to the Spreadsheet is stored in the Parameter data inside the Alibre files. You can see the link to the external information if you export a parameter from the Equation Editor. You can not change this information internally to Alibre, but you can Relink again to the spreadsheet from Alibre if necessary. This is helpful if you save the model to a new location and want to point to a new copy of the spreadsheet, or a new spreadsheet all together.

The interface within Excel only allows you to specify the external link information within the Alibre file, and the spreadsheet has no record of what has been linked. While you can drive multiple Alibre files with a single spreadsheet, there can be only one spreadsheet specified for a particular parameter. However you can specify a different spreadsheet for another parameter should you have a need. You can also drive multiple parameters from the same spreadsheet cell, and you can select those multiple parameters for linking at the same time (see Users Guide).

When you use the Add-in within Excel, it compares the Alibre file you select with the values in the Spreadsheet (based on the cell mapping within Alibre). If a difference is found, the APPLY button becomes active, and by pressing the APPLY button, the values in the Spreadsheet are then applied to the linked parameter(s) in Alibre. Note that this is 'Spreadsheet Driven' design, where the spreadsheet can 'drive' values in Alibre. It is a 'one way' transfer of information and not a 'link' to between Alibre and a Spreadsheet. It is a method where a value within Alibre can be updated externally. The concept being that the user only needs to work with a simple columnar interface (a.k.a Excel Spreadsheet) to easily manipulate the data that determines the characteristics of the Alibre model.

We will now create a spreadsheet that will drive the values for our 5 defining parameters that determine the characteristics of our Parametric Bookcase. For all of our parts and the bookcase assembly as well.

Step 1: Create an Excel Spreadsheet

Create a new folder called SPREADSHEET BOOKCASE, open your Parametric Bookcase Assembly and use Save As to make a copy of the entire bookcase in the new folder. It is important that you make a copy of your model before proceeding, because once you link the Spreadsheet to your model, you will NOT be able to modify the Driving Parameters manually. You will want to retain a non-spreadsheet version for further lessons.

Create a new Excel Spreadsheet and Save it in the SPREADSHEET BOOKCASE folder as Bookcase.xls. I created a very simple spreadsheet with the Driving Parameter labels in Column A, and the values for those labels in Column B.

Before you can use the Excel add-in, you need SAVE the spreadsheet to the file system. The pathname has to be established BEFORE it can be used to fill in the Alibre parameter information. So Save the spreadsheet to the SPREADSHEET BOOKCASE folder.

You can create significantly more complex spreadsheets that will have all the values for every aspect of an entire cabinet job--or single cabinet. The same techniques will apply as with this simple example for the tutorial.

Once we have created and saved the spreadsheet to the file system, we now need to link the Alibre files to the spreadsheet and the appropriate cells. REMEMBER, we are linking Alibre to this spreadsheet, and not the spreadsheet to Alibre. However you use the Alibre Add-in to accomplish this task from within the Excel program.

I will briefly cover the material on pages 222 thru 224 of the Alibre Users Guide, with emphasis on the important points. Please refer to the Users Guide for more detailed information on this process.

You do not need to have Alibre Design running while initially setting up the spreadsheet--Alibre Design HAS to be running for the Add-in to function, so start Alibre Design if it is not currently running.

While it is useful to be able to set up the link to Alibre files from 'Active Sessions' button, or the 'Repository' button for the Professional and Expert versions, we will rely on the 'File System' button for this tutorial. The other methods work exactly the same, with the 'Active Sessions' working on already open files. When you select the 'File System' button, you can browse to locate the Alibre file you wantâI chose the Side to startâthen it will open automatically so you can link the parameters.

Step 2: Control Parameters Dialog Box

With Alibre Design running, select the Excel spreadsheet window and use the menu selection Tools > Alibre Design Add-in > Control Parameters to start the add-in interface. It may be helpful to configure your Excel to always show the full menus, because the entry in my 'Tools' menu is all the way at the bottom. When we add a script to automate the process, it is also important that the menu entries not change location within the dialog.

When the Control Parameters window opens, it will be populated with the parameters and their values from the Alibre file.

The values will be in Centimeters, the units that Alibre uses internally to store all the parameter information. We will set the Units to match our information in the spreadsheet when we link to the spreadsheet cells.

Step 3: Prepare to Link Up

To link the Alibre parameters to the Spreadsheet, highlight the parameter to be linked in the Control Parameters dialog box Parameters window. When the selected parameter is shown in the Name field at the bottom of the Control Parameters dialog window, select button with the underscore for the Cell Reference field. This will close the Control Parameters dialog window and be ready for you to select the field in the spreadsheet with the matching value.

Step 4: Link Up

Click on the Value cell for the parameter that matches the parameter selected in the Control Parameters window. You want to select the actual value to be linked to the Alibre parameter and not any descriptive text. Alibre can only link a parameter to a single cell, and that needs to be the cell that contains the desired value for the selected parameter. The rest of the spreadsheet can be as much window dressing as desired to make the spreadsheet easy to understand and use.

The value does not have to be just a static value as in this case. It can be a cell that contains an equation. A spreadsheet can be constructed to contain all the equations that we included in the Parametric Bookcase, and then link to a very minimal model. However, I prefer to define my equations in Alibre because it leaves me with greater flexibility when driving my model from different sources.

When you have selected the appropriate cell, the border will begin to move. I have heard it referred to as marching ants. The cell location will appear in the Cell Reference field. Click on the icon to the right and it will return to the Control Parameter box.

Step 5: Changing Units

The selected cell will then fill the Cell Reference with the cell location in the spreadsheet.

Now you need to select the appropriate Units that match the value contained in the spreadsheet. It is very easy to overlook doing this, so be careful at this point. The Spreadsheet Add-in is unable to determine the units you have defined in the Alibre file, so you need to specify those units with each and every linked entry.

You can use any units you desire in the spreadsheet. Even if your Alibre file uses Inches for measurements, you can set up your spreadsheet with millimeter values, as long as you specify what units are used in the spreadsheet cell. We often include millimeter values when adding European hardware joinery information to our cabinets. Alibre handles the conversion quite nicely, and there is no need to do any manual conversion. Just provide the Units information when entering values.

Press the 'Modify' button, and the information provided at the bottom of the Control Parameters dialog box, will be updated to the Parameters table section.

Step 6: Finishing the Links

Repeat this procedure for each of the bookcase defining parameters until they are all linked to their appropriate spreadsheet cells. You can close Control Parameters and then save (if you have not already) and close the spreadsheet.

More importantly, you MUST save the Alibre file for these changes to be included as part of your model. If you do not save, the changes to the Alibre file will be lost. REMEMBER, this Add-in interface within Excel opened an Alibre file, and made modifications to it.

The unlinked parameter values and their displayed units do not matter, because they will be left unchanged and remain as you defined them within Alibre. You will not see the formulas created in the parametric model, but only their resultant values show up in the Control Parameters dialog. And if you link any of these parametric equation parameters to the spreadsheet, the formulas will be replaced by the link information for the spreadsheet cell.

Step 7: Now the Assembly!

We can add the same overall dimensional parameters to the Bookcase Assembly as with the parts, and replace the shelf spacing with a parametric equation as well. The spreadsheet link can now be used to update the common parameters in all part files and assembly files so that the entire model will update with changes to the defining parameters. For example, we can add all the new parameters with a value of zero in the Alibre Equation Editor.. As we link to the spreadsheet, pressing the Modify button in the spreadsheet dialog window will update the parameters to reflect the values in the spreadsheet.

There is one little trick I used in the ShelfSpacing equation. Alibre assumes any numbers entered in a equation are a distance and add on a units designation. In order to subtract just a plain number 1 from the Shelf_Quantity (in order to get the number of openings between the shelves) I defined a new parameter with a value of 1, and a type of 'Count'. This gives me a parameter I can use to subtract 1 from a number that is also defined as a type of Count by the Linear Pattern. I renamed the parameter that contains the count for the number of shelves to SHELF_QUANTITY. See Image 2.

After we update the defining parameters, we can replace the value for the shelf spacing with a parametric formula as well. The bookcase will then automatically adjust to any future changes in defining parameters. Also, Modify C1 to SHELF_QUANTITY and check that the Type 'Count' button is checked.

Now that the assembly is fully parametric as well, you can easily change the number of shelves, as well as the overall dimensions. Additionally, while you can fix the top and bottom shelves with biscuits, you can add some holes in the cabinet sides and make the middle shelves adjustable. You can even add another parameter for the amount to remove for adjustable shelf clearance...

Step 8: It Works!!!

With our Parametric Bookcase completed and fully linked to the Spreadsheet, we are now ready to use the Spreadsheet to drive the defining parameters for the Bookcase.

Open the Excel spreadsheet, modify the BOOKCASE_TOEHEIGHT to 4 inches. You can save the spreadsheet with the new values, but it is not necessary.

With Alibre running, start the Alibre Control Parameters Add-in within Excel, and use the File System button to browse to the first part in our Parametric Bookcase. When the part completes opening, you will see that there is now an entry in the New Value field for the BOOKCASE_TOEHIGHT. This means that the Add-in has found a difference between the Alibre file and the Spreadsheet, and is ready to update the Alibre file with the new value. Just press the APPLY button and EVERY field that has a New Value will be updated within the Alibre file. It will update all the changes found with a single step. Now select the Alibre file window and Save it, in order to save the changes made to the parameter values.

Repeat this process with each Part file for the Bookcase.

Step 9: Conclusion

Conclusion: While this might be a bit of work for such a simple bookcase model, again it only demonstrates the techniques that can be used for a much more complicated model. Luckily, I hate any dull repetitive work, and like to write Scripts to automate tasks within Alibre. I have written a simple script (macro) that will take the information you set up in a file and uses that to repeat this manual procedure for every file involved. This script/macro is running in the foreground instead of the user, and you need to remove your hands from the keyboard and mouse while it is running so as not to effect the keystrokes being sent to the computer.

The data file SpreadsheetUpdate.ini is included with the script for this bookcase, and consists of a line entry to define the name of the spreadsheet, and then the filenames for each Alibre file involved. It will process the Alibre files in order, and you should always put the highest level assembly file at the end. It will not matter for updating the values, but it will save you having to open the assembly files again after the updates to the part files. This script should work for models with greater complication as well.

Also included is the AutoHotKey source for the script so you can modify it as desired. The script works with Excel 2000 and 2003. If you have other versions you will have to update the script. The included .exe file will allow you to run the script without having to install AutoHotKey on your system. AutoHotKey is a free scripting language that has a large user community and excellent support forum. A worthy addition to your Alibre installation.

You can download a printable copy of this tutorial, the script file, and all the files associated with this parametric bookcase from the Alibre forum, in the AutoHotKey Scripts topic posted in the Binaries section.