In this article, we look at the second major new feature for Design Master Electrical 6: a link between the database and Microsoft Excel. The Excel link consists of two distinct elements. The User Interface (UI), which includes the menu and the dialogs, and the Excel functions. The functions are the real meat of the link. These are what actually put number in your spreadsheet. The UI is simply a convenient way to insert these functions. This distinction is important to remember. The UI is simple and quick to use. For 90% of your needs it will be sufficient. It might even do 100% of what you need if you do not ask it to do very much. But it is limited. It is not possible to have it do everything you might want it to do. In these cases, it is possible to manipulate the functions directly to create truly powerful spreadsheets. In this article, we will take a look at what is possible to do with only the UI.
Before you do anything in Excel, you have to select a database. When you select one, a new sheet called "DM Elec" is added to your workbook. Cell A1 gets a label and cell B1 gets the full path of the database. Because the location of the database is a cell in the spreadsheet, it is very easy to change it, making it simple to reuse the spreadsheet in a new project.
Once you have a link to a database established, you can begin inserting values from the database. Most of the values that we included in this release are quantities. As an example, let's take a look at inserting a light fixture quantity count. You can see a sample of the dialog to the right. You select the light fixtures that you want to count on the left. Each fixture will get its own line in the spreadsheet. To the right of that are the selection criteria for the light fixtures.
You can filter the count on a number of criteria:
These criteria can be mixed and matched to provide the quantity that you need. For example, you could count all of the new light fixtures of each type that are on your site plan drawing for a cost estimate. Or you could count all of the light fixtures, new and existing, in a specific room for a light budget analysis.
Once the values are inserted in Excel, you can then use standard techniques to create an interesting spreadsheet from them. For a cost estimate, you might include a column next to the quantity that is the cost per fixture and then another column that is the total cost of all of the fixtures. You can insert a sum at the bottom of the total cost column and you have the cost of all the light fixtures in the project. Now, as light fixtures are added and subtracted from the drawings, the quantities will update automatically, and you total cost will update. You can easily see the changes in the cost of the project.
Another use might be to try different lighting plans for a project. Say you have three different ideas about how the lights could be arranged, maybe each arrangment using a different set of fixtures. You could layout these arrangment on three different drawings. Then, using the drawing filter, you could create a cost estimate for each of the layouts. With just a little bit more work, you have a cost estimate for each of the different layouts. Now, as you decide which of the layouts fits your client's needs best, you can easily use the cost to guide the decision. For a budget-conscious client, you could simply take the cheapest of the three. For a client interested in a high quality lighting solution, you can show them the tradeoffs between cost and quality that are available and help them decide what they want to do.
There are two utility functions that are included with the Excel link. The first is a custom print function. Take a look at this cost estimate for breakers. There is a row for each combination of number of breakers, poles, and the rating of the breaker. With three poles and ratings ranging from 15A to 4000A, there are 108 rows displayed. There are a handful shown in the picture below. If you click on it, you will see what was fit on the first page of the cost estimate.
As you can see, most of those quantities are 0. However, which rows are 0 will vary from project to project, depending upon the sizes of the loads that are used. You would like to be able to create a cost estimate once with all of your breaker sizes, but you don't want to print reams of paper saying 0 breakers of most sizes. To accommodate this, we created a custom print utility that allows you to put the cursor on a column and have all of the 0 rows not print. Take a look at the print preview for the same set of breakers using this utility.
It condensed 108 rows to six. All of the 0's are gone and we are left with only the breakers that are actually used. If we later go back and add or subtract different types of breakers from our project, the quantities will be updated for us in this spreadsheet. When we run the print utility, it will take the latest data and only print the breakers that are currently being used.
The other utility allows you to replace all of the calculated fields in your spreadsheet with the actual values. When you send your spreadsheet out of the office to a client, they will not have the necessary software installed to view the results of the functions. Stripping out the functions and replacing them with the values creates a static picture of the sheet that you can send to anyone and they can view it. To protect your work, this function automatically creates a copy of the spreadsheet before removing all of the functions. You would not want to accidentally remove the functions, hit save, and ruin your spreadsheet!
Do you have feedback on this article? Let us know what you think!
©2001-2019, Design Master Software, Inc