The Art of Toolmaking

If he couldn’t find the right tool, my dad would just make his own. I actually think he liked this best. We had a skylight in our kitchen that was too high to easily open and close. After spending twenty minutes in the garage, my dad came back upstairs with a triumphant grin and a rough-looking mechanism he’d fashioned on the end of a stick. It wasn’t pretty but it sure made operating the skylight a lot easier.

Then there was the contraption he put on the bird feeder to keep the squirrels out. After nearly losing his voice from trying to scare away the squirrels one fall weekend, he headed to the garage and came out with a hungry squirrel’s worst nightmare. Half a sphere of polished aluminum and almost three feet in diameter, it fit under the free-standing bird feeder blocking access to the bounty of seeds and nuts above.

It looked terrible but it worked. . . for almost two days. Then the squirrels found a way in and my dad headed back to the garage with some new ideas and a determined look on his face. My father reveled in the art of toolmaking. 

Make your own tools

While I never learned how to machine metal from my dad, I did pick up his love of tool making. Using your ingenuity to solve your own problems is immensely satisfying. It’s the main reason I learned to program. What I also find satisfying is finding unconventional uses for conventional tools. Like my dad did with odds and ends in the garage, I love taking a random piece of software and using it in a way you never thought you could.

I recently put this into practice when I needed to issue a large set of drawings. When I worked in bigger firms, I was always the “lucky” person who packaged up the drawings at the end of a deadline. I would collect the PDF and DWG files, check them against the drawing index, and send them out to the client and consultants.

If you’ve ever done this, it’s definitely not glamorous work. That said, ensuring all deliverables are actually delivered is really important. There’s nothing more embarrassing than getting a call from the client on Monday morning wondering why they’re missing twenty sheets or why half of the sheets are rotated 90 degrees. Good quality control is essential for an effective architect.

One of the most basic tasks in this process is to check all the PDF and DWG files against the drawing index to make sure all the files are present and accounted for. It’s tedious work for sure, especially when the team is constantly updating the files and the drawing index.

The manual way to do this is to simply print out the index and check off each file that’s in the “to be issued” folder.  It’s total grunt work and really tedious, even more so if you need to do it multiple times throughout the day.

Faced with this task, I realized Excel could help me automate the process. Since I knew which sheets were going to be issued, I could write a macro that would check the list against all the files in the issue folder. I could also check for files that are in the folder but aren’t on the drawing list. 

Creating the Sheet Issue tool

Here’s the process I used to create the tool. If you want to try it out yourself, you can download the Excel file from the ArchSmarter toolbox. Don’t have access? You can sign-up here to get access to the free Excel and Revit macro files.

1. To start, I opened a new Excel file and created four worksheets. I renamed the worksheets “Index”, “PDF”, “DWG”, and “Folder Info”.
2. I copied all the sheet numbers of the sheets to be issued into the “Index” worksheet. If you have a lot of sheets, this process can be easily automated with a macro.
3. In the “Folder Info” worksheet, I added the folder paths for the PDF files and the DWG files in separate rows.
4. Since I didn’t want to manually enter the DWG and PDF files, I wrote a macro that automatically populated the “DWG” and “PDF” worksheets from the folders listed in the “Folder Info” worksheet.
5. Now that I had all the data I needed to compare, I created a new worksheet and named it “Results – Index”.
6. Next, I wrote a macro that read all the sheets to be issued from the “Index” worksheet and compared each sheet with the files in the “DWG” and “PDF” worksheets. The macro writes the sheet number to the “Results” worksheet then uses a function to check if that sheet exists in both the “DWG” and “PDF” worksheets. If it does, the macro writes “ok” in the respective DWG or PDF column. If the file doesn’t exist, the macro writes “MISSING” in the column. After the macro runs, you can quickly scan the “Results” list to see what files are missing. 
   
7. I also wanted to know if files were added to the “to be issued” folder but weren’t listed in the index, I created two more worksheets, one called “Not Listed – DWG” and “Not Listed – PDF”. I then wrote another macro that checked the files listed in the DWG and PDF worksheet against the drawing index. If a file was listed but did not appear in the index, its file name was added to the “Not Listed” worksheet. 
   
8. As the last step, I created a simple dashboard so I could update all of the worksheets simultaneously and get a quick count of missing and extra sheets. The dashboard is a worksheet that contains a few lines of text and an update button. When clicked, the update button runs a macro that first clears the “not listed” worksheets then runs a succession of functions. The first two functions get the DWG and PDF file lists from their respective folders. The next functions check the files against the drawing list. The counts shown in the dashboard use the COUNTA formula to get the file and sheet counts from the other worksheets.

The tool was a big success. It makes the drawing issue process a lot faster and much more accurate. I could easily run an update every few minutes. This gave us a much better picture of what work still remained at any given moment.You can download the sheet issue tool from the ArchSmarter Toolbox.