Thinking Outside Of The Box
As a software developer working in a particular software one tends to think only of solutions within that technology – within that box. Makes sense to me. But today I found that thinking outside the box is sometimes necessary to solve technology problems in ways that are almost impossible to imagine when your gaze is focused too tightly on “traditional” solutions.
I don’t trust my users. Not that I think they’re bad people, but because they’re users. Most of my users just use my apps and do not snoop around to see how it works. But there are always some who will poke around and for them I use protection – I call it “Safe Spreadsheeting” to paraphrase Dr. Ruth.
Protection of Excel worksheets is of course not perfect, but frankly for my purposes it is good enough (and the best I’ve got). So I protect worksheets and workbooks with a password. I also protect my VBA Projects with a password and therein lay my biggest recent problem.
Many of you know that there is no way to programmatically set, change or remove a VBProject password in Office VBA. I have just spent several frustrating hours trying to build a RELIABLE use of “Sendkeys” to allow me to change the password sequentially on over a thousand identically designed files that need surgery in the form of new modules. I searched and searched the Internet and all I could find was frustrated people and crappy attempts at code using Sendkeys to kludge away this problem.
I had concluded that the only way to do it in the end was manually – one file at a time until carpal tunnel sets in…… then my client (who is also a Unix consultant on the side) showed up with a product called “Workspace Macro” http://www.tethyssolutions.com/product.htm.
The user simply started recording, opened Excel, opened the first file listed in a directory full of our Excel files, switched to the VBE window, removed the password protection of the Project, saved the file into another directory, deleted the original file from within the File Open dialog and then closed Excel.
Then they started the program and told it to run as many times as there were files in the directory – and walked away. Slowly and methodically every file in the directory, one at a time, got its VBproject protection removed……. Aaagggghhhh!
Now I can run my VBA code to open each file and swap in the new modules and do whatever changes I need to do to each file as normal. They can even replace the Project passwords by reversing the process.
They thought outside the box. I lost several hours of consulting fees (I can’t charge them for me being a moron) and a big chunk of credibility (and then I go and admit it to you too ….duh), although I have worked for this client for 22 years and they have a wider perception of my capabilities thankfully.
So the lesson I’ve learned, once again, is to stand back from problems and “Think Outside The Damned Box !!” from now on. And stop thinking I might be somehow “special”…
Addendum:
Thanx to Tom Gleason for pointing out the availability of "Auto Hot Key" as an alternative http://www.autohotkey.com/ - actually the client also tried this product and it worked as well. As Glen pointed out this product has more of a programmabilioty story and may be more powerful for developers (yes we LOVE to make things complex don't we?).
Glen - you shoulda posted this yourself so people could go to your website as well (if you want them to - I'll leave that to you ;-) )
Dick