A few months ago at work I spearheaded the standardization of our digitization log spreadsheets – simple Excel files that let students track what they digitized, its unique identifier, number of scans per item, date of scan, notes, etc.
The reason for this was twofold:
- A standard template would hopefully make the students a little more productive in that they would not have to re-acclimate themselves to a 'new look' whenever they starting working on a different digitization project.
- A standard template would allow us to quickly merge all the spreadsheets into a temporary master Excel file and import that into Microsoft Access so we could run queries and make quick monthly reports. We used a Visual Basic for Applications (VBA) script called RDBMerge to make the master file. It's a fantastically well written piece of work.
Of course, it took several days to standardize the numerous existing spreadsheets but we got there. Now, all spreadsheets had the same naming convention (using the collection's unique identifier) and were placed in the same folder – which essentially made it a quasi-database that could be queried with the search functions inherent to the OS. We considered using an actual database, but honestly that causes its own set of problems with training, support, and other stuff – hence the decision to stick with Excel and employ RDBMerge/Access.
Now, over time with standardized spreadsheets one naturally begins to think, "Ah! We should have also added a field for … !"
Well, yes that's a pain. In a true database that's no big deal, but with individual files who wants to go through myriad spreadsheets and manually add a column or a formula, etc?
Not me.
I did it once and it took a few hours even using some minor automation in the form a macro I recorded in Excel.
Once was enough.
But the great – I mean truly great – thing though about recording macros in Microsoft Office is that the resultant VBA code – code that the act of recording a macro generates – is visible. And, thus, editable. Not to mention it's a great way to learn VBA syntax. In other words, if you record yourself doing a task in Excel, you can then actually see/edit the line item code that equates to your actions. Seems like a great tool for teaching some programming skills …
Anyway, I started to ask the lazy question, "How can I iterate a recorded macro/s over all spreadsheets in a given folder?"
After much digging, I found a great template script here. The script basically is a wrapper that can encapsulate the VBA script for one's recorded macros. A little know-how and tweaking is required, but that's better than the alternative: learning from-scratch Visual Basic on the job is something I really just don't have the time for right now. At any rate, I was able to use the template script and automate stuff across all of our digitization log files in no time.
By the way, if you use the script, note the snippet below shows a line break that isn't present in the blogger's post, perhaps due to their blogging software's formatting.
'will start LOOP until all files in folder sPath have been looped through
Set oWbk = Workbooks.Open(sPath & "" & sFil)
p.s. in my experience, batch VBA scripts break on Excel files for which sharing is turned on (simultaneous multi-user editing). Those files need to be identified* and temporarily isolated into another folder or the sharing needs to temporarily be turned off. Also, sheets need to be 'unprotected' for the code to work, but it's quite simple to add a line in your macro that unprotects the sheet prior to performing whatever macro you've created.
* It's a good idea to use some sort of suffix to identify shared files … something like "filename.share.xlsx". In fact, if one does so, a VBA script could be run to turn off sharing for all shared files (*.share.xls*). Unfortunately, trying to turn sharing off for a non-shared Excel file will break the script … which, again, is why it's best to identify shared documents through a filenaming convention.
--------------