How Can I Create a Folder With a Name Based on the Monday and Friday of the Coming Week?

ScriptingGuy1

Hey, Scripting Guy! Every Sunday I need to create a folder with a name similar to this: C:\Test\7-2-2007 to 7-6-2007, with the dates representing the Monday and the Friday of the upcoming week. How can I write a script that will create this folder for me?

— R

Hey, R. You, from time-to-time people write to the Scripting Guys and say, “You guys are so crazy; where does all that craziness come from?” Well, here’s one place. As it turns out, later this summer the Scripting Guy who writes this column will be accompanying selected family members on a trip to Italy.

At any rate, this past weekend the tourists-to-be were sitting around discussing the forthcoming trip. “At least this time I won’t be worried about the food,” said the Scripting Mom, with memories of Parisian croissants filled with snails still fresh in her mind. “I’ll just have spaghetti or some other kind of pasta at every meal.”

“I don’t know, Mom,” said the Scripting Sister. “You have to be careful in Italy, because spaghetti doesn’t necessarily mean spaghetti and meatballs. What if you order spaghetti and it comes with clams or something?”

“That won’t be a problem,” said the Scripting Mom. “I’ll just make sure I order spaghetti with marijuana sauce each time.”

Now, to the Scripting Mom’s credit she immediately corrected herself and said, “Marinara sauce. I meant marinara sauce.” Of course, by then the damage had already been done. “I don’t think you can get spaghetti with marijuana sauce in Rome,” noted one of the Scripting Brothers. “I think you’ll have to go to Amsterdam to get that.”

Anyway, if you’re wondering why the Scripting Guys are so crazy, well, at least some of that appears to be genetic. Fortunately, though, the Scripting Guys don’t spend all their time dining on spaghetti with marijuana sauce. Every now and then they pause for a moment and write scripts, scripts that do things like create a folder whose name includes the dates of the Monday and Friday of the coming week:

intDay = Weekday(Date)

If intDay = 1 Then
    intAdder = 1
Else
    intAdder = 9 – intDay
End If
dtmMonday = Date + intAdder
dtmFriday = dtmMonday + 4
strFolderName = “C:\Test\” & dtmMonday & ” to ” & dtmFriday
strFolderName = Replace(strFolderName, “/”, “-“)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFolder = objFSO.CreateFolder(strFolderName)

Whoa, man: the colors, look at the colors ….

Sorry; the Scripting Mom packed a lunch for us this morning. For some reason, today’s lunch seems to pack more of a wallop than the usual bologna sandwich and Twinkie.

As for the script, as best we can recall, that starts out by using the Weekday function to assign a value representing the current day of the week to a variable name intDay. In case you’re wondering, the Weekday function returns one of the following values, depending on the day of the week:

Why do we do care about the current day of the week? Well, we thought it would be useful to create a script that can be run at any time; as created, you can run this script on any day of the week, not just on Sunday. That seemed better, and a little more generic, than hard-coding in values that would cause unexpected results of the script ran on any day but Sunday. Of course, to be truly generic we could modify the script so that it asked the user to input a date, then created a folder for the week immediately following which date was entered. But, then again, the Scripting Guys never want to be truly generic; we just want to be sort of generic.

However, for those of you who don’t mind being truly generic, just replace the first line of the script with the following block of code:

dtmDate = InputBox(“Please enter a date: “, “Enter Date”)

dtmDate = CDate(dtmDate)
intDay = Weekday(dtmDate)

This block of code asks the user to enter a date, uses the CDate function to convert that value to a date-time value, then uses the Weekday function to determine the day of the week corresponding to the entered date. Pretty slick , huh?

Now, back to the question at hand: why do we care about knowing the current day of the week? That turns out to be an easy question to answer: our folder name has to start with the Monday of the following week, and we can’t determine the date of the next Monday without knowing whether today happens to be a Tuesday, a Wednesday, or a whatever. Let’s say that today is a Saturday; in that case, we’re going to have to add 2 days to the current date to get the date of the next Monday. What if today is a Monday? In that case, we’ll have to add 7 days to the current date to get the date of the next Monday. That’s what this bit of code is for:

If intDay = 1 Then
    intAdder = 1
Else
    intAdder = 9 – intDay
End If

This code might not make a lot of sense at first, but it works. (Trust us.) What we’re doing here is checking to see if intDay is equal to 1, meaning that we’re running the script on a Sunday; if it is, then we assign 1 to a new variable named intAdder. As the name at least kind of implies, we’re going to use intAdder to add the requisite number of days to the current date, eventually ending up with the date of the next Monday. If the script is run on a Sunday, then we have just 1 day until the next Monday. Consequently, we assign the value 1 to intAdder.

If we run the script on any day but a Sunday, however, we execute this line of code instead:

intAdder = 9 – intDay

Had we wanted to, we could have set up a bunch of If Then statements, one for each day of the week. (For example, if intDay equals 2, then add 7 days to the current date.) We wanted to find a way to bypass writing all those If Then statements, however, so we used this simple little algorithm instead: subtract the integer value of the current day from 9 and, lo and behold, we’ll get the back the number of days we need to add. For example, suppose today is a Tuesday, which has an integer value of 3. Subtract 3 from 9 and we get 6. Amazingly enough, 6 also happens to be number of days we need to add to a Tuesday in order to get the value of the very next Monday.

And no, you haven’t been slipped some of the Scripting Mom’s spaghetti. Play with this a little bit and you’ll see how it works.

Now we’re ready to start calculating dates and creating folder names. To begin with, we determine the date of the next Monday by taking the current date and adding the value of intAdder:

dtmMonday = Date + intAdder

If today happens to be Monday, July 2, 2007 then intAdder will be 7; adding 7 to July 2, 2007 makes the variable dtmMonday equal to this: July 9, 2007.

If you thought that calculating the value of the next Monday was easy, wait until you see how easy it is to calculate the value of the next Friday; all you have to do is add 4 days to the date of the next Monday:

dtmFriday = dtmMonday + 4

As soon as we have our two dates we can then start putting together our folder name. Step 1 in that process is to execute this line of code:

strFolderName = “C:\Test\” & dtmMonday & ” to ” & dtmFriday

One small word of caution here: depending on your language and regional settings you might have to modify the code used to create the folder name. On the Scripting Guys’ test computer, dates are displayed in this format: Month/Day/Year. Thus the preceding line of code concatenates the following values:

That’s going to result in the following folder path:

C:\Test\7/7/2007 to 7/6/2007

That’s close to what we want, except for all those / characters. Those aren’t allowed in folder names; therefore, we use our next line of code the replace each / with a dash (-):

strFolderName = Replace(strFolderName, “/”, “-“)

That gives us a folder path that looks like this:

C:\Test\7-7-2007 to 7-6-2007

That also means we’re now ready to create an instance of the Scripting.FileSystemObject object and then use the CreateFolder method to create our new folder:

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFolder = objFSO.CreateFolder(strFolderName)

At that point, we’re done. Which can only mean one thing: it must be time for more spaghetti with …marinara… sauce. If you need us, we’ll be in the lunchroom.

For the rest of the day.

ScriptingGuy1

Follow