Sample Macro

Run Macro in Variable to Convert a Date

Written by Kevin Heaton, Insight Software Solutions, Inc.

What it does

This sample macro shows how to change a date from MM-DD-YY to DD-MMM-YYYY format using the ‘Run Macro in Variable’ command.

To install

Download the macro file umrunmacroinvariable.mex and save it to your hard drive. Open the macro file or import the macro into your existing macro file by clicking File, Import, Import Macros.

To use

This sample macro can be run by right-clicking on it in the Macro Explorer window and choosing the ‘Run Macro Now’ option. The result will be displayed in a Text Box. However, this macro will provide the most benefit if you study how it works and modify it for your own use.

How it works

Every macro command can be viewed two different ways: as it appears in the Script Editor and in ASCII format as it appears in the Direct Editor.

There is a macro command that allows you to choose a date such as 07/10/08 and have the system return it in a specified format such as 10-Jul-2008. But, there is a problem. Unlike most other macro commands, this command does not allow you to use a variable as the starting date.

This command looks like this in the Script Editor:

Date/Time: Save “DD-MMM-YYYY” into %T11%

Using 07/10/08 as a starting date, converting it to the format DD-MM-YYYY and putting the result into %T11%, the command would look like this in the Direct Editor:

<DT:MM-DD-YYYY|T:11:2:7/10/2008|12:00:00 AM>

What we are going to do is put this command into a variable and then run the macro in the variable. The result of doing this is that the modified date will be placed in variable %T11%.

First we need to substitute variables for the month, day and year instead of the hard-coded ‘7/10/2008’. We will assume that the month is in variable %T15%, the day is in variable %T16% and the year is in %T17%. The command now looks like this:

<DT:MM-DD-YYYY|T:11:2:%T15%/%T16%/%T17%|12:00:00 AM>

Note that the | that appears twice in this command is actually a special separator character. We cannot reliably enter the separator character into a variable so we will substitute \s for each separator character. The string now looks like this:

<DT:MM-DD-YYYY\sT:11:2:%T15%/%T16%/%T17%\s12:00:00 AM>

Since we are only interested in converting the date we can ignore the time (12:00:00 AM).

So the trick is to set variable %T9% to this string and use the ‘Run Macro in Variable’ command to place the result in variable %T11%.


 – Macro Express Pro