Hey, Scripting Guy! How can I configure Office Excel to autosave every 5 minutes?

— KK

Hey, KK. You know, that’s a good question. As it turns out, the autosave capability in Microsoft Excel – hey, wait a minute: do you realize what day it is? Today is the first day of the 2007 Winter Scripting Games; why in the world are you reading the Hey, Scripting Guy! column?!? After all, even as we speak you should be writing a script that calculates the Chinese astrological year or unscrambling scrambled scripting terms. Quit wasting your time and get going on the Games!

Um, not that Hey, Scripting Guy! is a waste of time, mind you; it’s just that the Scripting Games are special: after all, they represent the opportunity for you to match your scripting skills with script writers from all around the world. That’s an opportunity that only comes once a year, which means you don’t want to miss it.

Besides, you aren’t going to win a Dr. Scripto bobblehead by reading this column, that’s for sure.

On the other hand, KK, you’re probably one of those people who have already finished every event in every division. Last year, during the inaugural Scripting Games, we worried about whether we had made the events too hard and whether we gave people enough time to write their scripts. Those concerns were answered soon enough: just a few hours into day 1 of the 2006 Games we’d already received 300 completed (and correct) entries. And no doubt many of you wrote those scripts with one hand tied behind your backs to boot.

Show-offs!

So what the heck: if you have time to compete in the 2007 Scripting Games and to configure Excel to autosave every 5 minutes, well, we’ll do what we can to help you out:

Set objExcel = CreateObject("Excel.Application")

objExcel.AutoRecover.Enabled = True
objExcel.AutoRecover.Time = 5

As you can see, this is a simple-enough little script, even for the Scripting Guys (who really did think that the events in last year’s Scripting Games were challenging). We start out by creating an instance of the Excel.Application object. In this script, however, we don’t bother setting the Visible property to True. Why not? Because configuring the autosave time is going to take only a split second; it just seemed silly to show Excel onscreen and then immediately make it disappear. But if you’d like to see Excel anyway, just so you know that something is happening, then make this the second line of code in the script:

objExcel.Visible = true

After creating our instance of Excel we then configure two different property values. To begin with, we set the value of the AutoRecover.Enabled property to True; that simply makes sure that autosave is actually enabled. What if you want to disable autosave? You got it: just set Autorecover.Enabled to False:

objExcel.AutoRecover.Enabled = False

Next we set the value of the AutoRecover.Time property to 5 which, needless to say, sets the autosave interval to 5 minutes. Want to autosave every 20 minutes? No problem; just use this code instead:

objExcel.AutoRecover.Time = 20

And that’s all we have to do; we don’t even have to call the Quit method and explicitly dismiss Excel. That’s because we didn’t add a workbook to our instance of Excel; in a case like that, when you don’t add a workbook, Excel automatically terminates at the same time the script terminates.

Incidentally, if you’d like to verify that your script worked you can use this code to retrieve the values of the Enabled, Time, and AutoRecover.Path properties:

Set objExcel = CreateObject("Excel.Application")

Wscript.Echo "AutoRecover enabled: " & objExcel.AutoRecover.Enabled
Wscript.Echo "AutoRecover interval: " & objExcel.AutoRecover.Time
Wscript.Echo "AutoRecover path: " & objExcel.AutoRecover.Path 

In turn, that returns information similar to this:

AutoRecover enabled: True
AutoRecover interval: 10
AutoRecover path: C:\Documents and Settings\kenmyer\Application Data\Microsoft\Excel\
We hope that helps, KK. And we hope to see all of you take part in the Scripting Games: the Games are fun, they’re challenging, and there are some cool prizes to be won. The truth is, there’s really no reason not to take part.
Well, unless, of course, you’re too much of a chicken.





Note. The Scripting Editor has just informed us that we can’t call our readers chickens; please accept our apologies. Instead, today’s concluding sentence should have read as follows:
Well, unless, of course, you’re too much of a scaredy-cat.
Thank you. And good luck in the Games!