How Can I Create a New Excel Spreadsheet at Midnight Each Night?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! How can I create a new Excel spreadsheet at midnight each night? I’d like each spreadsheet to have a file name similar to this: 6-1-06.xls.

— DH

SpacerHey, Scripting Guy! AnswerScript Center

Hey, DH. You know, often-times the simplest approach is also the best approach. For example, the other night the Scripting Son was having a frustrating baseball game: in his first two at-bats he hit the ball about as hard as anyone could hit it, yet he also hit it right at an outfielder. Two great at-bats and nothing to show for it. So what did he do? He simplified matters. “This time I’m hitting it where no one can catch it,” he muttered as he went to the plate for at-bat number 3. Seconds later he smashed a mammoth, three-run, game-winning home run.

Note. Hey, who said that, in just one at-bat, the Scripting Son did something – hit a home run – that the Scripting Dad couldn’t do in all his years of baseball? We are shocked that someone could even think something like that. Not that it isn’t true; we just don’t like to be reminded of it.

Besides, the Scripting Dad was a leadoff hitter, and leadoff hitters never hit home runs. And no, we haven’t heard of Rickey Henderson. Or Craig Biggio. Or Alfonso Soriano. Or – say, shouldn’t we be talking about scripting?!?

OK, so maybe the Scripting Dad never hit a home run in his life. He did, however, come up with a script that can automatically create a new Excel spreadsheet based on and named after the current date. And he also came up with a simple way to make sure that script runs at midnight each night, something we’ll talk about in a few minutes.

But first things first:

dtmDate = Date

strMonth = Month(Date) strDay = Day(Date) strYear = Right(Year(Date),2)

strFileName = “C:\Scripts\” & strMonth & “-” & strDay & “-” & strYear & “.xls”

Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add() objWorkbook.SaveAs(strFileName)

objExcel.Quit

Like we said, simplicity is a virtue. The script starts out by using the VBScript Date function to store the current date in a variable named dtmDate. We then use the functions Month, Day, and Year to grab individual pieces of that date and store them in the variables strMonth, strDay, and strYear. We should point out that, when extracting the year, we also use the Right function and grab only the two rightmost characters. Why? So that the variable strYear will be equal to 06 instead of 2006. If you’re OK with having all four digits of the year in the file name then that particular line of code can be simplified to look like this:

strYear = Year(Date)

Next we need to construct the full path to the new Excel file, something we do with this line of code. If the date happens to be June 1, 2006 the value of the variable strFileName (and thus the new file name) will be C:\Scripts\6-1-06.xls:

strFileName = “C:\Scripts\” & strMonth & “-” & strDay & “-” & strYear & “.xls”

If you’re having problems seeing how we came up with C:\Scripts\6-1-06.xls it might help to take a look at the individual items we’re combining in that line of code:

C:\Scripts\

6 (the value of strMonth)

1 (the value of strDay)

06 (the value of strYear)

.xls

Or, to put it pseudo-mathematically:

C:\Scripts\ + 6 + – + 1 + – +06 +.xls = C:\Scripts\6-1-06.xls

To tell you the truth, deriving the path name is the most difficult part of the entire script (and even that wasn’t too-terribly hard). Once we have the path name we then create an instance of the Excel.Application object and set the Visible property to True. (Incidentally, you don’t have to set the Visible property to True. We did it in this sample script simply so you’d have a visual cue as to whether or not the script was actually working.)

With Excel up and running we then use this line of code to create a new workbook:

Set objWorkbook = objExcel.Workbooks.Add()

And as soon as we have a new workbook we can call the SaveAs method, passing the path name we just created as the sole parameter:

objWorkbook.SaveAs(strFileName)

Believe it or not, that’s all we need to do. SaveAs will save a new, blank spreadsheet file for us, and we can then use the Quit method to terminate Excel. Simple, yet effective.

Of course, that’s all well and good, except for one thing: how do we make sure that the script runs every night at midnight? Admittedly we could write some fairly complicated code that runs in the background, periodically checking the date and time, and then creating a new spreadsheet each time the clock strikes midnight. Like we said we could do that, but: 1) that sounds like a lot of work; and, 2) you have the problem of figuring out what to do if the script ever stops for some reason. (Not to mention figuring out how’d you even know that the script has stopped.) Believing whole-heartedly in the concept of simplicity, we decided to sidestep all those problems and instead schedule the script to run as a Scheduled Task.

What’s that? You say you’ve tried running scripts as Scheduled Tasks and they never worked? That’s probably because you listed the script itself as the program to run. For example, in the Scheduled Tasks wizard you might have entered something similar to this in the Select Program to Schedule dialog box:

Scheduled Tasks


And you’re right: that probably won’t work. What you need to do instead is pick one of the script hosts (either Cscript.exe or Wscript.exe) as the program to run, and then specify your script as a command-line parameter for that program. To do that, select one of the script hosts (for example, C:\Windows\System32\Cscript.exe) as the program to be scheduled:

Scheduled Tasks


When you get to the last page of the Scheduled Tasks Wizard don’t just click Finish. Instead, select the checkbox labeled Open advanced properties for this task when I click Finish checkbox and then click Finish:

Scheduled Tasks


Once you’ve done that, wait for the dialog box to appear and then add the path to your script (for example, C:\Scripts\Test.vbs) as a command-line parameter to Cscript.exe:

Scheduled Tasks


In other words, the Scheduled Task should show something like this in the Run box:

C:\WINDOWS\system32\cscript.exe c:\scripts\test.vbs

That should take care of any problems you’ve had trying to run scripts as scheduled tasks.

Note. Sure, you can write scripts to create, delete, and otherwise manage scheduled tasks, although there are some limitations. For more information, check out this portion of the Microsoft Windows 2000 Scripting Guide.

And no, the Scripting Dad never hit an inside-the-park home run, either. And his teams never won a single championship, another sore spot seeing as how the Scripting Son has won everything from league championships to city championships to district all-star championships. Of course, the Scripting Dad is one of the Microsoft Scripting Guys. We’d like to see the Scripting Son top that!

Yes, yes, you’re right: hitting a game-winning home run does top being a Scripting Guy, hands-down. But, hey, what doesn’t?

0 comments

Discussion is closed.

Feedback usabilla icon