SPREADSHEET- DRIVEN PARAMETRIC BOOKCASE

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.

Comments

[farmhand]

I just downloaded Alibre today having used Soildworks with furniture for 12 years. The maintenance fees are killing me. I have always positioned my drawings top view, top plane. The instructions you gave;

"Create Parts and Assemblies with attention to view orientation. The standard Orthographic views of Front, Top, Right and Isometric should reflect those orientations for the Parts and Assemblies. The Front view of a part should be face of a board or molding, and not an edge or end. The Front view of an Assembly should be the front of a piece of furniture, cabinet, or when unclear something reasonable like the face of a bed headboard or footboard. Considering these orientations while creating parts and assemblies will make creation of a drawing easier, as well as provide a smooth and consistent workflow."

My question is, looking for a good reason to change from how I have been doing it. Or maybe I don't understand what you are saying.
Thanks for the instructions.

[Myself]

The way it was written, I thought it was an instructavertisement by the company itself. ;) Very thorough, and very useful, though. Just yesterday I was wishing there was a piece of software like this to generate tower designs for communications or wind turbines. Given a table of commonly available steel stock and the math for stress analysis, you should be able to punch in the desired load capacity and height, and out would come a parts list and assembly instructions. That would drop the price of towers significantly. :) Can you recommend a few sites with introductions to parametric design in general? The concept is something I'd like to explore further.

[woodknot]

There are many parametric solid modeling programs for Mechanical Design, and a good place to start is www.cadalyst.com. Cadalyst is a magazine and website devoted to the CAD industry, and has quite a few reviews of software. Alibre is also a Mechanical Design program, intended for engineering tasks such as yours, but with a focus on providing this parametric capability for a price more attractive to the independent engineer, consultant, or even businesses wanting something more affordable. Most of this software is quite expensive. What we have been trying to provide instruction for is how to take this mechanical design product and applying it to the woodworking industry. Particularly the small woodworking shop where there is a low profit margin and most software capable of this level of design is priced well out of reach. So you will have to excuse our enthusiasm now that we have found software that will let us do this level of design, at a price that woodworkers can actually afford. But, now that you mention it, I have not seem much on using the equations to design work such as this. Most programs instruct you on the capabilities of the program, and not so much on how to use it in your field. We have been focusing more on how to use the software to accomplish particular tasks. Quite frankly, this sort of design is a more advanced use of the software, but our intent is to show that once you master the basics, it is not really that hard to use all the power of parametric equations. We spent quite a bit of time trying many different software packages, and we wanted to show what set Alibre apart from some of the other design packages we evaluated, and why we selected Alibre to do our design work.

[woodworks]

Sorry about the dry technical style of the tutorial, but when my wife "woodknot" finished her Parametric Bookcase tutorial, I thought the Spreadsheet driven aspect would be a good compliment to her "Parametric" series. My background is Mechanical Engineering and as you noticed, she says I write like an "Engineer". Thankfully, my wife took the edge off a bit and hopefully made the topic a bit more understandable. I was a bit dubious as first when she started the "Parametric Design" series of tutorials, but as she aptly pointed out to me, it was this parametric design capability that sold us on Alibre. And, that it was important to show how to make the best use of this capability. These same concepts work with the other parametric software out there, just that how to create and link the equations is different. As you look at a particular brand of software, search the web for user written tutorials, as they are often the most informative.

[nagutron]

Wow. Detailed and informative Instructable. You should get in contact with Alibre and show it to them; companies sometimes forget that their users can sometimes write the best documentation for their products.