{"id":67293,"date":"2006-05-18T17:51:00","date_gmt":"2006-05-18T17:51:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/05\/18\/how-can-i-create-a-new-excel-spreadsheet-at-midnight-each-night\/"},"modified":"2006-05-18T17:51:00","modified_gmt":"2006-05-18T17:51:00","slug":"how-can-i-create-a-new-excel-spreadsheet-at-midnight-each-night","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-create-a-new-excel-spreadsheet-at-midnight-each-night\/","title":{"rendered":"How Can I Create a New Excel Spreadsheet at Midnight Each Night?"},"content":{"rendered":"

\"Hey, \n

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

— DH<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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. \u201cThis time I\u2019m hitting it where no one can catch it,\u201d 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. <\/P>\n\n<\/THEAD>\n\n\n
\n

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

Besides, the Scripting Dad was a leadoff hitter, and leadoff hitters never hit home runs. And no, we haven\u2019t<\/I> heard of Rickey Henderson. Or Craig Biggio. Or Alfonso Soriano. Or – say, shouldn\u2019t we be talking about scripting<\/I>?!?<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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\u2019ll talk about in a few minutes.<\/P>\n

But first things first:<\/P>

dtmDate = Date<\/p>\n
strMonth = Month(Date)\nstrDay = Day(Date)\nstrYear = Right(Year(Date),2)<\/p>\n
strFileName = “C:\\Scripts\\” & strMonth & “-” & strDay & “-” & strYear & “.xls”<\/p>\n
Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True<\/p>\n
Set objWorkbook = objExcel.Workbooks.Add()\nobjWorkbook.SaveAs(strFileName)<\/p>\n
objExcel.Quit\n<\/PRE>\n
Like we said, simplicity is a virtue. The script starts out by using the VBScript Date<\/B> function to store the current date in a variable named dtmDate. We then use the functions Month<\/B>, Day<\/B>, and Year<\/B> 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<\/B> function and grab only the two rightmost characters. Why? So that the variable strYear will be equal to 06 instead of 2006. If you\u2019re 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:<\/P>
strYear = Year(Date)\n<\/PRE>\n
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<\/B>:<\/P>
strFileName = “C:\\Scripts\\” & strMonth & “-” & strDay & “-” & strYear & “.xls”\n<\/PRE>\n
If you\u2019re 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\u2019re combining in that line of code:<\/P>\n\n\n\n\n\n\n\n\n\n
\u2022<\/TD>\n\n
C:\\Scripts\\<\/B><\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
6<\/B> (the value of strMonth)<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
–<\/B><\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
1<\/B> (the value of strDay)<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
–<\/B><\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
06<\/B> (the value of strYear)<\/P><\/TD><\/TR>\n
\u2022<\/TD>\n\n
.xls<\/B><\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
Or, to put it pseudo-mathematically:<\/P>
C:\\Scripts\\ + 6 + – + 1 + – +06 +.xls = C:\\Scripts\\6-1-06.xls\n<\/PRE>\n
To tell you the truth, deriving the path name is the most difficult part of the entire script (and even that wasn\u2019t too-terribly hard). Once we have the path name we then create an instance of the Excel.Application<\/B> object and set the Visible<\/B> property to True. (Incidentally, you don\u2019t have<\/I> to set the Visible property to True. We did it in this sample script simply so you\u2019d have a visual cue as to whether or not the script was actually working.) <\/P>\n
With Excel up and running we then use this line of code to create a new workbook:<\/P>
Set objWorkbook = objExcel.Workbooks.Add()\n<\/PRE>\n
And as soon as we have a new workbook we can call the SaveAs<\/B> method, passing the path name we just created as the sole parameter:<\/P>
objWorkbook.SaveAs(strFileName)\n<\/PRE>\n
Believe it or not, that\u2019s all we need to do. SaveAs will save a new, blank spreadsheet file for us, and we can then use the Quit<\/B> method to terminate Excel. Simple, yet effective.<\/P>\n
Of course, that\u2019s 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<\/I> 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\u2019d you even know<\/I> 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.<\/P>\n
What\u2019s that? You say you\u2019ve tried running scripts as Scheduled Tasks and they never worked? That\u2019s 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<\/B> dialog box:<\/P>\"Scheduled \n

And you\u2019re right: that probably won\u2019t 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: <\/P>\"Scheduled \n

When you get to the last page of the Scheduled Tasks Wizard don\u2019t just click Finish<\/B>. Instead, select the checkbox labeled Open advanced properties for this task when I click Finish<\/B> checkbox and then<\/I> click Finish<\/B>:<\/P>\"Scheduled \n

Once you\u2019ve 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:<\/P>\"Scheduled \n

In other words, the Scheduled Task should show something like this<\/I> in the Run<\/B> box:<\/P>
C:\\WINDOWS\\system32\\cscript.exe c:\\scripts\\test.vbs\n<\/PRE>\n
That should take care of any problems you\u2019ve had trying to run scripts as scheduled tasks.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. 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<\/B><\/A> of the Microsoft Windows 2000 Scripting Guide<\/I>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
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<\/I> one of the Microsoft Scripting Guys. We\u2019d like to see the Scripting Son top that<\/I>!<\/P>\n
Yes, yes, you\u2019re right: hitting a game-winning home run does<\/I> top being a Scripting Guy, hands-down. But, hey, what doesn\u2019t?<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
Hey, Scripting Guy! How can I create a new Excel spreadsheet at midnight each night? I\u2019d like each spreadsheet to have a file name similar to this: 6-1-06.xls.— DH 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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[710,711,48,49,3,5],"class_list":["post-67293","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-excel-spreadsheet","tag-excel-application","tag-microsoft-excel","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
Hey, Scripting Guy! How can I create a new Excel spreadsheet at midnight each night? I\u2019d like each spreadsheet to have a file name similar to this: 6-1-06.xls.— DH 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67293","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=67293"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67293\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=67293"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67293"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67293"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}