VBA is a hammer. It’s crude, but wonderfully efficient at getting things done. I don’t do a lot of “dirty work” anymore, but part of working at a small company is rolling up your sleeves every once in awhile.
The situation at hand: we have 20,000 XML files that need to have information that is currently contained in an Excel spreadsheet inserted into them. Each file is uniquely named and sitting in the same directory. In fact, I had looped through the files originally to populate the spreadsheet and a co-worker matched the information to certain IDs that needed to be plugged back in…
My fundamental mistake was not thinking through it ahead of time. I looped through the files and inserted the data into the spreadsheet – a row for each file. I also included a guid from the file header (inside file!!!!!!!!) into the spreadsheet. I thought that would be the easiest way create the match when I got the spreadsheet back. (I’m sure those of you deal with data already recognize my mistake.)
I got the file back, went back to reverse my loop and realized I now needed a second nested loop to go through each row of the spreadsheet looking to match the guid….
Because each file is uniquely named, I could’ve written the name of the file into a column and simply looped through the spreadsheet writing the guid to each file with name of Column X.
The worst part of this is that I completely berated a younger coleague a couple years ago for a nearly identical project. His response is the same that mine is now, “hey, it was just a one off project that I was doing on the side.”
Wise Man Say, “Pay Attention to Everything You Do. Little Stuff Matters Too. Otherwise You Will Suck.”
Popularity: 2% [?]