Restoring the Last 8 Seconds

For the last couple of years I’ve been running a particular report generation every week. This Excel custom VBA program acquires new data with Monarch, of course, and creates a distributable file that shelters the recipients of the file from the behind the scenes calculations required to get to the end result. One of the last steps in the process copies the worksheets that contain final formatted reports into a fresh new Excel workbook. On average, the worksheet copy from one workbook to another took about 8 seconds.

Then, in early October, it all fell apart.

The bulk of the process was running almost normally, if a bit sluggishly. The precursor to the grand finale was taking about 5 minutes longer than its normal 10 minutes. Not a big deal, I thought. I can tolerate that.

But the last steps went from eight seconds to a ridiculous length of time to complete: upwards of 10 minutes!

Of course, by now we’re all trained now to think, “What changed?” What was recently installed on the computer?

Hmm. Nothing. No new software. No Windows updates.

OK then, perhaps the Excel file that I use to create the reports, the one with all the behind the scenes stuff, somehow became corrupted. As a test, I created a new file with similar size and structures, and duplicated a sheet copy. I fully expected to discover that copying a sheet from this document to a new workbook would work normally, and would take mere seconds. Of course, that didn’t happen. The test took about 10 minutes, just like the file that I suspected to be corrupt.

Great. What now?

The really odd thing was that everything else on the computer seemed to be working just fine. Email. Web. Word processing. Everything was fine. Except that this rogue report generation was forcing me to work 14 hour days and stressing out completely every time, because it made no sense.

Naturally, I did what everyone does. Google it. This lead to my trying out the ideas I found that others had recommended for Excel performance issues. Manually clearing out the Temp folder, and adjusting the size of the Windows swap file. I even removed all of my personalized Excel macros, and my custom toolbars, looking for something, anything that was interfering somehow.

None of it worked.

Then, out of sheer desperation, in one fell swoop I deleted all of network printers that I had setup on my system except the three that I absolutely needed. Working on a large network, I had another dozen or so that I used infrequently.

And that was it. The copy process returned from taking 10 minutes to complete to requiring mere seconds.

Hours of research and trial and error and frustration disappeared with a solution that took, oh, about 8 seconds to accomplish.

At least one of those printers – I’ll never know which one – was causing the delay. Another strange symptom was that I never saw measurable network activity in the Task Manager, or other processes taking up CPU cycles.

Hopefully I’ll never see that problem again. I won’t get messages asking if the reports will be published soon. I won’t be the last one in the office. Again.

I’ll just get back to reliably using Excel with Monarch.