Marcus' Macro Scheduler Blog

This is something that comes up often, so I thought I’d mention it here. Every so often we get asked how to put a date into a filename. Say you want to rename or save a file with the date in the filename formatted to reportsYYYYMMDD.txt e.g.:

reports20061801.txt

Well the simplest way to do this is to use the basic date functions Year, Month and Day. These each return the current year, month and day numbers. We also need to embed the variables into the filename. We can create the filename like this:

Year>the_year
Month>the_month
Day>the_day
Let>filename=reports%the_year%%the_month%%the_day%.txt

See how we embed the variables by putting % symbols around them. The % symbols tell Macro Scheduler to find the variable within them and to use the value assigned to that variable. So on 18th January 2006 we get:

the_year=2006
the_month=01
the_day=18

And filename becomes:

filename=reports20060118.txt

Say we wanted to rename the reports.txt file to a reports20060118.txt. Let’s also say that the file is in the c:\data folder. We would do this:

MoveFile>c:\data\reports.txt,c:\data\%filename%

Or we could have said:

Let>filename=c:\data\reports%the_year%%the_month%%the_day%.txt
MoveFile>c:\data\reports.txt,filename

You might not be renaming a file, but sending the filename to an application. Most applications use the standard ‘Save As’ dialog. Once this has opened you can send the full path and filename directly to that box and hit the Enter key to perform the save. So after sending the keystrokes required to open the Save As dialog (often File/Save As) we’d do:

WaitWindowOpen>Save As
Send>c:\data\reports%the_year%%the_month%%the_day%.txt
Press Enter

The Year command returns the year in full 4 digit form. Suppose you only wanted the last two digits (06 in this case). Well we can use the MidStr command to extract the last two digits:

Year>the_year
MidStr>the_year,3,2,the_year

This says: Starting from the third character in the_year take two characters and return the result in the_year. In this case because the_year has previously been declared as a variable (by the Year command) it is seen as a variable. The result of the MidStr command is also the_year so it over writes the_year with the new value. We could have made a new variables:

MidStr>the_year,3,2,YY

Here we end up with a new variable, YY, which contains 06.

To understand this better, paste the following code into the Script Editor. Then open the Watch List (Debug/Show Watch List). Now place the cursor on the first line and hit F8. Each time you hit F8 the script will advance to the next line. You will see the new variables being created in the watch list to the right. And you will see their values changing as you advance through the script.

Year>the_year
Month>the_month
Day>the_day
Let>filename=reports%the_year%%the_month%%the_day%.txt

MidStr>the_year,3,2,YY
Let>newfile=outcome%YY%.dat

You might want to use a previous or future date. For more advanced date manipulation I recommend using the VBScript date functions.

Let’s say we want to use yesterday’s date in the filename, instead of today’s. To determine past or future dates based on today’s date use the VBScript function DateAdd. DateAdd takes an interval type, a number and a date. To add one day to the current date do:

DateAdd(“d”,1,Date)

To subtract one day from the current date we use:

DateAdd(“d”,-1,Date)

So to create a dated filename based on yesterday’s date we can do this:

VBSTART
VBEND
VBEval>DateAdd(“d”,-1,Date),yesterday
VBEval>Year(“%yesterday%”),yyyy
VBEval>Month(“%yesterday%”),mm
VBEval>Day(“%yesterday%”),dd

Let>filename=reports%yyyy%%mm%%dd%.txt

Here we use VBScript’s version of the Year, Month and Day functions which operate on a given date. First we subtract one from the current date to get yesterday’s date and we use that in the Year, Month and Day functions to extract the relevant parts and then we can construct our filename.

See the VBScript Documentation for more info on using VBScript functions.