Marcus' Macro Scheduler Blog

Here’s a quick way to extract just the filename from a full path:

Let>file_path=C:\Program files\Skype\Phone\Skype.exe

//get the file name on its own
Separate>file_path,\,parts
Let>exe_name=parts_%parts_count%

//now get just the folder path
MidStr>file_path,1,{length(%file_path%)-length(%exe_name%)},folder_only

This uses Separate to explode the path into an array using the ‘\’ character as the delimiter. We’ve called the array “parts”. “parts_count” contains the number of elements created. So parts_%parts_count% is the last element, which happens to be the filename.

Now, if we want just the folder path, use MidStr to extract length of file_path minus length of exe_name.

NB: For some reason the curly brackets in the last line of code above are causing the text to output on the next line. That should all be one line. Click on “view plain” to see the raw code if you want to copy it to Macro Scheduler.

VBScript and Complex Expressions use the ” (double quote) character to delimit strings. Native MacroScript code doesn’t need delimited strings. But if you need to use Complex Expressions or VBScript you need to remember that strings must be delimited in quotes.

One implication of this is that if the string already has a ” character within it you will get a syntax error, because the parser thinks this marks the end of the string.

To avoid this you need to add another quote, and you can use StringReplace to replace any occurences of the ” character with a second one before passing the string to VBScript or a Complex Expression:

//example input string:
Let>mystring=your name is "Sally"

//Double quote before using string in complex expression
StringReplace>mystring,","",mystring_2
If>{%mystring_2% <> ""}
  //Do something
Endif

Without that StringReplace call to double quote the quote characters, the complex If line would produce a syntax error.

The same goes for VBScript:

//example input string:
Let>mystring=your name is "Sally"

VBSTART
VBEND

StringReplace>mystring,","",mystring_2
VBEval>MsgBox("%mystring_2%"),nul

Note that the VBEval statement evaluates a pure VBScript expression. Since VBScript strings must be in quotes we surround the variable mystring_2 with them, and need to use % symbols to tell Macro Scheduler to use the value of mystring_2. See my last post for tips on when and where to use % symbols.

On a similar note, CR LF pairs (Carriage Return and Line Feed) in strings passed to VBScript will produce errors since they are hard line breaks and cause string termination problems. We can avoid this by replacing the real CR and LF characters with the VBScript placeholders:

StringReplace>myString,CRLF," & vbCRLF & ",myString

The quotes terminate and start the string again, splitting it where the CRLF was, and replacing the hard CRLF with the VBScript vbCRLF variable. There’s also vbCR and vbLF variables for carriage returns (CR) and line feeds (LF) on their own.

By default in Macro Scheduler you do not have to do anything special to refer to a variable. The following will create a variable called Name:

Let>Name=Freddy

Name is now assigned the value of Freddy.

Referring to Variables:

The following will display a message box containing the word “Freddy”:

MessageModal>Name

Macro Scheduler knows that Name is a variable and finds its value. You do not need to tell it that Name is a variable. If Name did not exist as a variable the message box would simply display the word “Name”.

If you prefer you can tell Macro Scheduler that Name is a variable like so:

MessageModal>%Name%

But, by default (see below to see how to change this behaviour), there’s really no need since Name appears on its own.

However, supposing you wanted a message box that said “My Name is Freddy” and you wanted to use the variable “Name”. In this case you need to tell Macro Scheduler that Name is a variable, like so:

MessageModal>My Name is %Name%

Hopefully the reason is clear. If we did not put % symbols around the second “Name” the message box would simply display “My Name is Name”. Equally if Macro Scheduler tried to interpret every word as a variable we’d end up with “My Freddy is Freddy”.

Rule of thumb: % symbols really only need to be used when a variable appears within another string. When used on their own they are not needed.

Creating/Modifying Variables:

% symbols mean find the value of this variable. So when creating or modifying variables do not use them. E.g., the following assigns the value Freddy to the variable “Name”

Let>Name=Freddy

If you were to include Name in % symbols then you’d be saying “Assign Freddy to the value of Name”. This could get confusing. Consider the following code:

Let>Name=Sally
Let>%Name%=Freddy

Here we’d end up with two variables, one called Name assigned the value of Sally, and another called Sally assigned the value of Freddy. Now this can be quite powerful and there may be times you want to do this, but for most of us, it is usually not what we want.

Rule of thumb: Do not use % symbols when assigning to variables. This includes variables returned by other commands as well as variables on the left of the equals sign in Let commands.

There’s an interesting forum topic on this subject here.

Exceptions:

One time we DO want to use % symbols on the left side of a Let command is when we are creating/modifying array type variables and we are using an array counter. E.g.:

Let>x=0
Repeat>x
  Let>x=x+1
  Random>9,var
  Let>Array[%x%]=var
Until>x=10

Here x is the loop counter. We are creating an array of 10 random values. In this case we want the value of x inside the array name and end up with variables Array[1], Array[2], Array[3], …. Array[10]. This is one case where we want a variable inside a variable.

Explicit Variable Resolution:

Real programmers prefer to tell the programming language when a value is a literal and when it is a variable, and as you get more advanced you may find the need to do that. You can tell Macro Scheduler to only resolve variables that are contained in % symbols and leave everything else as literals by setting VAREXPLICIT to 1:

Let>VAREXPLICIT=1

With this setting the following code will display the word “Name” in the message box:

Let>VAREXPLICIT=1
Let>Name=Fred
MessageModal>Name

Unlike before where we’d see “Fred” in the message box. Now to see the value of the Name variable we’d have to do:

MessageModal>%Name%

Note that when assigning to variables the variable name still appears on its own. Nothing changes in regards to creating or assigning to variables; only when referring to their values. The rules above still apply.

For more information see the following topics in the help file:
* User Defined Variables
* Explicit Variable Resolution
* Ignoring Spaces

If you have macros in Microsoft Word, Excel or Access they will be written in VBA – Visual Basic for Applications. If you wish to use this code inside Macro Scheduler you can convert this code to VBScript. However you cannot just copy the code and paste it into Macro Scheduler – or even into a VBScript file – and expect it to work, because there are some key differences:

1. Objects belonging to the application are automatically exposed to VBA in that application, but don’t exist outside of it.
2. VBA supports “Named Argument Syntax”. VBScript does not.
3. Named Constants are automatically defined in the container application but meaningless outside of it.

So, let’s deal with each of these in turn and look at what we need to do about them.

Application Objects, Methods and Properties

Objects belonging to the container application – e.g. Excel – are automatically exposed to VBA within that application. All objects belonging to Excel can be used directly by VBA and your VBA can refer to them directly without having to create them. But outside of Excel these objects do not exist, so the code will fail – Macro Scheduler/VBScript doesn’t know what they are. You need to create them. This is done with the CreateObject command. At the very least you will need to create the “root” object which is usually the .Application object:

Set ExcelApp = CreateObject("Excel.Application")

Now you can refer to objects and properties belonging to Excel by proceeding them with the object variable:

ExcelApp.Visible = true
ExcelApp.Workbooks.Open("d:\example.xls")
ExcelApp.Workbooks.Worksheets("Sheet2").Activate

If you record a simple macro in Excel which enters some values into some cells you might see code like the following created:

Range("E4").Select
ActiveCell.FormulaR1C1 = "fred"

This code is clearly relative to the active sheet. It doesn’t specify which sheet should be used. And the Sheet object is clearly automatically exposed. To do the above outside of Excel we’d need to reference the correct Sheet object, which belongs to a Workbook object, which of course belongs to the Excel application object. So you could do:

ExcelApp.ActiveWorkbook.Sheets("Sheet1").Range("E4").FormulaR1C1 = "fred"

But you might split things up a bit with:

Set ExcelApp = CreateObject("Excel.Application")
Set MyBook = ExcelApp.Workbooks.Open("d:\example.xls")
Set MySheet = MyBook.Worksheets("Sheet1")

MySheet.Range("E4").Value = "Harry"

Note that the first three lines create references to the Application, Workbook and Worksheet objects. It’s then easier to refer directly to the objects, properties and methods belonging to each of those objects.

So where in a VBA macro inside Excel you may just see Range(“E4″).Value=”1234” consider what the Range object belongs to and remember you need to create a reference to that object, and then prefix it with the name you give to that object reference.

Each Office application includes a Visual Basic reference in the help system. In there you can see how all the objects refer to each other. E.g. if you find the help topic for the Range object you will see that it belongs to the Worksheets object.

Named Argument Syntax

VBA supports something called “Named Argument Syntax” in function calls (ArgName:=ArgValue). E.g. record a macro to sort a column in Excel and you will see something like this in the generated code:

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("F4:F1048576"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= xlSortNormal

Note Key:=Range(… and SortOn:=xlSortOnValues etc. The benefit of this system is that if a function accepts lots of optional parameters but you only need to set a few of them and leave the rest to their default values, you specify just the arguments you want to include.

However, VBScript does not support this system and instead requires all the function parameter values only in the order in which they are declared. So the above would look like this in VBScript:

MySheet.Sort.SortFields.Add MySheet.Range(F4:F1048576"), xlSortOnValues, xlAscending, xlSortNormal

As mentioned, Named Argument Syntax means that not all the parameters need to be passed to the method and because they are named can be passed in any order. So when converting to VBScript be sure to view the help for the function and find out what parameters it expects and in what order.

Named Constants

Named constants that belong to the application will also mean nothing to Macro Scheduler/VBScript. Inside of the application they belong to they are exposed to VBA. They mean something to VBA inside of the application. But take the code outside of the application and the names are meaningless.

For example the code above uses three named constants: xlSortOnValues, xlAscending and xlSortNormal. As these are declared automatically within Excel they mean something to VBA. Outside of VBA, in VBScript/Macro Scheduler they will cause errors because they are undeclared.

We need to declare these:

xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0

I know what you’re thinking – how do I know that xlSortOnValues equals 0, xlAscending is 1 and xlSortNormal is 0? Well, you could look them up. But I don’t bother doing that. I use the VBA debugger. Open up the Visual Basic Editor and hit CTRL+G to open up the “Immediate” pane – it may already be visible. Inside the “Immediate” pane type:

?xlSortOnValues

And press Enter. You’ll see the value of xlSortOnValues appear on the next line. Handy eh?

Conclusion

The key thing to remember is that your Office VBA is referring to objects that exist only within the application in question. Your Excel code can say just Range(“E5”) because it knows what a Range object is. Macro Scheduler has no idea what “Range” is unless you tell it. Once you understand that you need to create references to these objects, and look at the VBA help to understand the hierarchy of objects, the process of porting your code to VBScript should begin to make more sense.

You’ll find an example script that controls Excel with VBScript installed with Macro Scheduler. This post contains similar code. You’ll also find lots of examples in the forums. Hint: try searching for “CreateObject”.

UPDATE: 25/09/2012 – Macro Scheduler now includes some native Excel functions (XLOpen, XLGetCell, XLSetCell) etc. DDE is also no longer supported in the latest version of Excel. Using DBQuery, as described here, is still a very useful way to retrieve Excel data, especially for doing SQL style lookups.

Last July I wrote this post summarising three different ways Macro Scheduler can read/modify Excel data including using DDE to quickly retrieve/modify cells, and VBScript to script pretty much anything in Excel. Example scripts demonstrating both methods, and an example.xls file ship with Macro Scheduler.

Well, now with the native database functions there’s another way. DBConnect can connect to Excel and treat it as a database, using one of the following connection strings:

OLE DB:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\myfolder\workbook.xls; Extended Properties=”Excel 8.0; HDR=No;”

ODBC:
Driver={Microsoft Excel Driver (*.xls)}; DriverId=790; Dbq=c:\myfolder\workbook.xls;

Either should work if you have Excel installed. Note that HDR=No in the first connection string tells ConnectDB to retrieve the first row. Without it the first row is treated as column names and not retrieved. This is not supported in the second method. See Microsoft KB257819 for more info and other options.

So the following code will retrieve the entire contents of Sheet1 into an array:

Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%SCRIPT_DIR%\example.xls;Extended Properties="Excel 8.0;Hdr=No;"
DBConnect>connStr,dbH

Let>SQL=select * from [Sheet1$]
DBQuery>dbH,SQL,rsSheet1,nR,nF

DBClose>dbH

This reads everything in Sheet1 from example.xls which is stored in the same folder as the macro (SCRIPT_DIR). Just modify the path in the Data Source= part of the connection string to point to a different workbook.

The entire sheet is now in the rsSheet1 array. nR contains the number of rows and nF the number of columns (records and fields). So rsSheet1 looks like:

rsSheet1_1_1 .. nsSheet_1_nF
..
rsSheet1_nR_1 .. nsSheet_nR_nF

A nice quick way of sucking an entire worksheet into a MacroScript array.

Note that you have to use [Sheetname$] as the table name. According to Microsoft you can also use named ranges, or unnamed ranges:

Named Range:

SELECT * FROM MyRange

Unnamed Range:

SELECT * FROM [Sheet1$A1:B10]

To insert/modify data you first need to name the columns in your worksheet. You can then do something like:

INSERT INTO [Sheet1$]([First Name], [Last Name]) VALUES (‘John’, ‘Smith’)

Where “First Name” and “Last Name” are names given to columns. The data will be added at the first blank row.

See also:
Using Macro Scheduler’s Database Functions
Methods for Accessing Excel Data

Update: Here’s a connection string for Excel 2007:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%SCRIPT_DIR%\example.xlsx;Extended Properties="Excel 12.0 Xml;HDR=Yes";

Dick Lockey has written a nice example in Scripts ‘n Tips showing a good use of the Include statement. Include allows you to include other scripts in your code. A good use of this is to keep commonly used subroutines, VBScript code, or dialogs in scripts that you can then Include in your macros, rather than duplicating the code across all the macros that use it. If you ever need to fix or modify this code you then only need to do it once. It also makes your main scripts smaller and easier to read.

Dick’s example provides a function for validating dialog field lengths. Useful in itself.

Read the example here.