VB Nested Loop Algorithm Problem

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% [?]

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • MisterWong
  • Y!GG
  • Webnews
  • Digg
  • del.icio.us
  • StumbleUpon
  • Reddit

2 comments to VB Nested Loop Algorithm Problem

  • Dude, did you start on this yesterday? Maybe you need a middle tier. Sorry guys; inside joke from some good times many years ago.

    You know, things were so much easier when we knew we could use VBA to conquer pretty much any problem. Now we have VSTO and managed code, blah, blah, blah.

    What happened to the good ol’ days when we could whip up a macro and drop it into production on the fly? I miss those days.

  • scott

    I miss those days!

    Nothing like wiping out a couple hundred users by dropping a file on the public drive.

    I wonder what ever happened to Jordan Block?

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Sponsors