My boss won’t care. He expects it. No one I work with will understand it, or care. It’s not their accomplishment.
I won’t be recognized for it. Maybe that’s fine; I sort of put it together and sort of had help. Maybe I’m just too full of myself to realize this isn’t what I’m cracking it up to be.
But I did something today, and when I left here on Friday, I didn’t know how in the world I was going to get it done.
The puzzle is, I have to make Excel do something Excel isn’t inherently tuned to do. It doesn’t do this by itself, and whether that’s because it’s too hard for their development team to fix, or whether it’s not something they’re going to bother with, the little “feature” (read: BUG) in Excel is annoying and has to be worked around.
The bug is this: When you create a chart in Excel, if the chart points at data on a different sheet, copying those sheets to a new workbook won’t work the way you expect. Excel’s chart formula – which is called a “Series formula” if you’re curious – still points back to the original workbook, from which the chart and data has been copied.
So I’ve got ChartBook1.xls. I copy Sheet_Chart and Sheet_Data to a new workbook. Instead of NewWorkbook’s chart point to NewWorkbook(“Sheet_Data”), it points to OldWorkbook(“Sheet_Data”) instead. No one even noticed until we sent this data out to someone who didn’t have access to the drive where the old workbook lived. When that happens…well. And if we try to send it to an outside party, say one of our customers?
So my task became, fix it. Fix it fast. Fix it permanently.
I did some research and found one very helpful website, but it didn’t solve the issue at first blush. So I had to use something called an array to do the sheet copying. That is, I take all the sheets I want to copy and put their sheet names in an array. Copy the array to a new workbook. As long as the sheets with the charts and their associated data sheets are all in the array, then Excel sets them to look at the right data.
So, doing it this way, NewWorkbook(“Sheet_Chart”) now uses NewWorkbook(“Sheet_Data”) for its data, which is exactly what we want. Was it easy? Well…yes and no. It was an easy fix to implement, but it was harder than the dickens to get to the answer. Maybe I’ve got brain fog due to too much Merlot and Chinese food over the weekend, or maybe I’m just a dull stone. But I couldn’t figure it out based on the stuff I found on the Internet.
The Internet stuff, however, did lead me to try an experiment. I did the copying from OldWorkbook to NewWorkbook by hand first. No dice if I just copied the charts, then tried copying the data sheets. Same if I did it backward and copied the data sheets first, then the charts. The only way this worked is if I copied all the sheets at one pass, by holding down the Shift key and choosing each tab to copy. Follow the steps as usual and voila! I get a nice, clean copy in a new book, with the charts pointed at the right data sheets (the copied ones, not the originals).
So, I do that again, only this time I’m recording the action as a macro. When it’s done I go look at the code and have a V8 moment, realizing how stupid simple this is from a code perspective.
I jump back to my macro workbook and figure out where to add the stupid-simple code. I test it. BINGO! Winner-winner, Chicken Dinner!
(I don’t like chicken that much. How ’bout steak? With Merlot.)
It works. I make all my dev-to-prod adjustments, put the new macro version into the main drive, and test again. DING-DING-DING-DING! Poultry cuisine again.
Nice. And done.
All told, I started this around 8AM today, and hammered away at a bunch of failed tests until I finally got it…the stupid-simple way, of course, which I should have tried first. *Sigh* Ah well. It works, it’s done, and I’m glad to be rid of that POS.
On to the next one.
See ya soon.