Hey, Scripting Guy! How can I delete all the worksheets in an Excel spreadsheet except for the first worksheet?

— NN

Hey, NN. Please excuse any bloodstains that might appear on your screen; as it turns out, the Scripting Guy who writes this column was involved in an … incident … this past weekend. Ah, but dedicated Microsoft employee that he is, he’s already back at work, and back to writing this column.

In case you’re wondering, this weekend the Scripting Guy who writes this column and the Scripting Son were playing wiffle ball in the backyard. The Scripting Guy who writes this column somehow managed to hit three home runs on three consecutive pitches. The Scripting Son, who was none-too-pleased with this surprising turn of events, decided it would be … funny … to throw the next pitch as hard as he could. (That’s something you typically don’t do, seeing as how, in this particular game, the pitcher stands maybe 20 feet from the hitter in the first place.) The Scripting Guy who writes this column managed to duck out of the way of the pitch, but the ball hit the storage shed, bounded off into the dense jungle in back of the house, and rolled down a tiny – but steep – ravine.

That’s where the fun began. Down to their last wiffle ball, the Scripting Dad and Son stood at the top of the ravine. “Hey, I see the ball,” said the Scripting Dad. “I think I can get it.” And with that he began inching his way down the hill, balanced precariously on a tangle of dead branches and dead blackberry bushes.

“Just let it go, Dad,” said the Scripting Son. “That stuff you’re walking on doesn’t look very stable. Let’s just go buy another ball.”

“Don’t worry,” said the Scripting Dad. “I know what I’m doing.”

As it turned out, what he was doing was walking on a pile of dead branches and dead blackberry bushes, a pile that soon gave way, pitching the Scripting Dad headfirst down the hill and into the blackberry bushes below.

You might think that landing face first in a bunch of blackberry bushes would hurt. You would be right about that.

Fortunately, other than a number of blackberry thorns that had to be removed from his hands, legs, and face, the Scripting Guy who writes this column was fine. In fact, once he stopped bleeding, the whole thing was actually pretty funny.

Or at least the Scripting Son thought so.

But all’s well that ends well, right? After all, the Scripting Guy who writes this column is still alive (or at least as alive as he’s ever been), and everyone else still gets a script that deletes all the worksheets in an Excel spreadsheet except for the very first worksheet:

Set objExcel = CreateObject(“Excel.Application”)

objExcel.Visible = True
objExcel.DisplayAlerts = False
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
i = objWorkbook.Worksheets.Count
Do Until i = 1 
  objWorkbook.Worksheets(i).Delete
  i = i – 1
Loop

Provided we don’t fall down another ravine in the process we’ll take a few minutes here to explain how this script works. As you can see, we begin by creating an instance of the Excel.Application object and then setting the Visible property to True; that gives us a running instance of Excel that we can view on screen. That brings us to this line of code:

objExcel.DisplayAlerts = False

What’s this line for? Well, if you try to delete an Excel worksheet and that worksheet contains data, a dialog box will pop up asking if you really want to delete this worksheet. (This happens regardless of whether you use a script or you try to delete the worksheet from within Excel itself.) We don’t really want to see that dialog box; after all, that would cause our script to stop each time we attempt to delete a worksheet, and, even worse, to stay stopped until we answer the dialog box. However, if we set the DisplayAlerts property to False that prevents the dialog box from appearing; instead, the script will act as though the dialog box had appeared and, in turn, as though we had clicked the default button. In this case, at least, that’s perfect: that’s because the default button happens to be Yes (meaning that we do want to delete the worksheet).

After configuring the DisplayAlerts property we next call the Open method to open the workbook C:\Scripts\Test.xls:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)

And once the file is open we use the Count property to determine the number of worksheets in Test.xls, storing that value in a counter variable named i:

i = objWorkbook.Worksheets.Count

Now we’re ready to start deleting worksheets.

Of course, deleting items from Microsoft Office collections can be tricky; that’s due to the way Office renumbers items any time something gets deleted from a collection. One way to work around this problem is to start at the tail-end of the collection and work your way back towards the beginning. With that in mind we set up a Do Until loop that looks like this:

Do Until i = 1 
  objWorkbook.Worksheets(i).Delete
  i = i – 1
Loop

Sure, we’ll explain how this loop works. Let’s say we have 5 worksheets in our collection. What we’re going to do is start off by deleting the last worksheet in the collection (which has an index number of 5). That’s what this line of code does:

objWorkbook.Worksheets(i).Delete

This works because we’ve assigned the number of worksheets to the counter variable i; as a result, the value of our counter variable (5) is equal to the index number of the last worksheet in the collection (also 5). After deleting worksheet 5 we decrement i by 1; if you do the math, that means i is now equal to 4. We loop around, delete worksheet 4, decrement i by 1, then return to the top of the loop and do the whole thing all over again. This continues until i is equal to 1. When i is equal to 1 that means we only have one worksheet in the collection; thanks to a little planning and foresight we also know that this lone worksheet is the very first worksheet in the collection. And because we’ve now deleted all the worksheets except for the very first one, that means we can go ahead and exit the loop and let the script terminate.

And there you have it: as easy as falling off a log. Or at least as easy as falling off a pile of dead branches and dead blackberry vines.

Speaking of which, if you’re waiting for the punch line to today’s story, well here it is. As long as he was lying at the bottom of the ravine, the Scripting Guy who writes this column figured he might as well retrieve the wiffle ball. When he did, he discovered that there was now a big crack in the ball and it was no longer usable. In other words, he ended up having to go out and buy another ball anyway; he just took a slightly more circuitous (and painful) route than he needed to.