Excel Automation Projects
Excel automation has become part of my core skillset. I have found that especially if a company already relies heavily on Excel, automating their workbooks can drastically reduce workload, streamline workflows, and increase flexibility.
Output Solidworks Files
At Technetics, we would use Excel to calculate design-related parameters when quoting customers.
Previously, once this analysis was complete, the assembly would be sketched, extruded, mated, and drawings created.
I implemented Excel modules that would communicate directly with Solidworks to create corresponding models, assemblies, and drawings. This was done through copying template files from a hidden location, altering their global variables, and referencing the new file locations in the assemblies.
This automation approximately cuts the time to quote a job in half. All that is left for the engineer to do once models are output is update the global variables later on if the design changes or make slight modifications to the drawings.
Streamline Workflow
The workbook mentioned above was also altered to streamline other parts of the design process.
Previously, performing functions such as pulling a part number or tool number would require accessing a separate database. In addition, calculating results for design variations with and without guides would be done by hand, making it difficult to organize results properly.
I implemented several sections in the workbook to simplify this process for design engineers. First, I utilized functions to interact directly with outside Access databases and other Excel workbooks to properly pull and log part and tool numbers. The goal of this improvement was to stop engineers from having to hop all over the network to find what they need.
Second, I included a section allowing the user to view different variations of the design (with guides, no guides, between guides, etc.). This improvement not only made it easier to calculate and compare design variations, but also to present them to other engineers for review.
Lock Down Signoffs
It is important that operator signoffs on work instructions are properly restricted. If they are left able to be manipulated, accidental and purposeful edits can be made by individuals at any point.
Previously, operator signoffs were left unlocked on work instructions. Though this never led to any issues, it was a gap in our certainty that signoffs are legitimate.
Because all of these work instructions were created in Excel, I was tasked with locking down these signoffs.
Because these workbooks were shared, I was unable to simply use VBA to lock and unlock cells. Instead, I settled on the method of linking every signoff cell to a hidden sheet. When a signoff was completed, VBA modules would check where the next free signoff slot was and sign off the hidden sheet.
All of the links and signoff buttons were placed using a separate workbook. This way, the engineer only needed to give this workbook the file path to the work instructions, and the code would handle the rest.
In addition, employee signatures were linked to computer logins. This made it impossible for an operator to mistakenly sign off for someone else.