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

With the latest release (10.1.15) we worked on optimizing the way variables are stored internally and were able to dramatically improve performance. Dick Lockey did some tests and writes that the latest version is 40 times faster than earlier versions!

If you’re working with scripts that create and process a large number of variables - say a script which reads and processes a large number of fields from a database - you’ll want to make sure you’re using the latest release, because your scripts will be noticeably faster with this version.

Macro Scheduler History

There are various scripts on the forums demonstrating how to automate Internet Explorer via VBScript and IE’s Document Object Model. Such as this one: Automate web forms with IE

These require that the script creates the Internet Explorer instance via a CreateObject call. The script then has access to the IE object and can access its methods and properties in order to automate navigation, clicking on links, filling forms and extracting data from the page, etc.

A question that has come up a few times is “How do I connect to an already running copy of IE, one that the script didn’t start?”. This is what the GetObject function is designed for, but Internet Explorer doesn’t support it.

Well I recently discovered a solution. The following script has a subroutine called GetIE which will find a running copy of IE and set the global IE object variable to that copy. This can then be used in other functions to control that copy of IE:

VBSTART

Dim IE

'Attaches to an existing instance of IE with matching URL
Sub GetIE(URL)
  Dim objInstances, objIE
  Set objInstances = CreateObject("Shell.Application").windows
  If objInstances.Count > 0 Then '/// make sure we have instances open.
    For Each objIE In objInstances
	  If InStr(objIE.LocationURL,URL) > 0 then
	    Set IE = objIE
	  End if
    Next
  End if
End Sub

'fills a form field and optionally submits form
Sub WebFormFill(fieldname,fieldvalue,submit)
  Dim FormNr
  Dim ItemNr
  Dim TheForm

  if IE.Document.All.Tags("FORM").Length = 0 then
    MsgBox("No form found in page")
  else
    for FormNr = 0 to IE.Document.Forms.Length - 1
      Set TheForm = IE.Document.Forms(FormNr)
      for ItemNr = 0 to TheForm.Elements.Length - 1
        if TheForm.Elements(ItemNr).Name = fieldname then
          TheForm.Elements(ItemNr).Value = fieldvalue
          If submit=1 then
            TheForm.submit
          end if
          exit for
        end if
      next
    next
  end if
End Sub

'Navigates IE to specified URL
Sub Navigate(URL)
  IE.Navigate URL
  do while IE.Busy
  loop
End Sub

'clicks specified link
Sub ClickLink(linktext)
  Dim anchors
  Dim ItemNr

  Set anchors = IE.document.getElementsbyTagname("a")
  For ItemNr = 0 to anchors.length - 1
    If anchors.Item(ItemNr).innertext = linktext Then
	  anchors.Item(ItemNr).click
	End If
  next

  do while IE.Busy
  loop
End Sub

'This function extracts text from a specific tag by name and index
'e.g. TABLE,0 (1st Table element) or P,1 (2nd Paragraph element)
'set all to 1 to extract all HTML, 0 for only inside text without HTML
Function ExtractTag(TagName,Num,all)
  dim t
  set t = IE.document.getElementsbyTagname(Tagname)
  if all=1 then
    ExtractTag = t.Item(Num).outerHTML
  else
    ExtractTag = t.Item(Num).innerText
  end if
End Function

VBEND

//Find existing IE window
VBRun>GetIE,www.google.com

//enter search query and submit
VBRun>WebFormFill,q,windows macro,1

//Click a link
VBRun>ClickLink,Advanced Search

//extract
VBEval>ExtractTag("td",5,0),tagText
MessageModal>tagText

The implementation of GetIE shown here accepts a string. GetIE then looks for a copy of IE whose location URL contains that string. So it allows you to find a copy of IE based on the URL it is currently at.

This example attaches to a copy of IE currently open at www.google.com. So to run this example first open IE and go to www.google.com. Then run the script and it will enter a search term, click the Advanced Search link and then extract some text from a TD tag.

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”.

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

Macro Scheduler 10.1 includes four functions for connecting to databases, querying and modifying data:

• DBConnect
• DBQuery
• DBExec
• DBClose

Connecting to a Database

Before you can connect to a database you’ll need to make sure you have the required OLE DB/ODBC drivers installed. You can see what drivers are already installed under Control Panel > Administrative Tools > Data Sources (ODBC). If you have Microsoft Office installed you’ll already have the standard Microsoft ones for Microsoft Access, Excel, dBase, Paradox etc. You may also already have the driver for Microsoft SQL Server installed.

If you want to connect to a third party database such as Oracle, Sybase, MySQL etc, then you may need to install the required drivers. Although if your computer is running other software which already accesses these databases, you probably already have the drivers installed. If not, visit your provider’s web site to find the required drivers, or dig out those install disks, or contact your system/database administrator!

DBConnect is used to create a connection to a database. It requires an ADO, OLE DB, or ODBC Connection String, and returns a handle to the database, which is used in the other database functions.

What is a Connection String?

A Connection String is just a string containing database connection information, telling Macro Scheduler how to connect to the database in question. The string contains a number of arguments and values separated by semicolons:

argument1=value1; argument2=value2;

What these arguments and values should be depends on the database you are using and the method of connection. More on that in a moment.

It is possible to create DSN (Data Source Name) connections in your Control Panel. All this does really is help you build a connection string and store it in your registry. Then the Connection String in DBConnect just has to be the DSN name you defined in Control Panel. While this method makes it easier to create the connection, it is obviously less portable. If you want to use this method to create a system DSN go to Control Panel > Administrative Tools > Data Sources (ODBC). You can then choose from the installed providers and the dialogs will ask for the connection information needed.

How do I Construct a Connection String?

The correct answer is to read the documentation for the database you want to connect to! But as people have been using connection strings to connect to databases since I still had a full head of hair, the web abounds with useful information. Google is your friend. Look what comes up top with this Google Search:

Position 1: http://www.connectionstrings.com

Why, some web site called nothing other than connectionstrings.com, created by some kind soul who clearly read your mind.

Click on the database type you want to connect to and you’ll be shown everything you need. For example, if you want to connect to Microsoft SQL Server you’d probably need:

Driver={SQL Server}; Server=myServerAddress; Database=myDataBase; Uid=myUsername; Pwd=myPassword;

You’ll note there are other options depending on what kind of security is required and how the server is configured and so on. In most cases the basic string is probably all you need, but if in doubt, or if it fails to work, contact your database administrator. Yes, contact your database administrator, not me. Seriously. Whoever set your database up will have more of a clue than I.

So, anyway, let’s put the above into a DBConnect call in Macro Scheduler. We’d do something like:

Let>connstr=Driver={SQL Server}; Server=myServerAddress; Database=myDataBase; Uid=myUsername; Pwd=myPassword;
DBConnect>connstr,dbH

Obviously, you’ll need to repace myServerAddress with the name or address of the server, myDataBase with the name of the database, myUsername with a valid username with permissions to do whatever you plan to do with database, and myPassword with your password.

Want to connect to a different type of database? First, make sure you have the right ODBC/OLE DB drivers installed. Second, read the documentation and if possible speak to your database administrator (if that’s not you!) and if still unsure try Googling “Connection Strings”.

Security Issues

Your administrator may have locked down the port that the ODBC driver connects through. Make sure your IP address can connect through that port. Some database servers require the database user to have special privileges to be able to connect remotely. Again, these are all things for your system administrator to help you with. You don’t have a system administrator? You’re the one who sets it all up? Well, I guess you’ll have to read the docs again then. Sorry

Retrieving Data

To retrieve data from the database we use the DBQuery command. This accepts a valid SQL statement which returns a recordset, e.g. a SELECT statement. Now, I am not going to try to teach SQL here. There are heaps of resources out there that do that already. A quick Google search reveals this tutorial. Also, most databases provide utilities which help you build queries graphically and chuck out the SQL for you.

The most basic SELECT statement is: “SELECT * FROM TABLENAME”, e.g.:

SELECT * FROM CUSTOMERS

Which just says “select all records from the CUSTOMERS table”.

As well as a SQL statement DBQuery needs a database reference returned by our previous DBConnect call, so that it knows which database you want to perform the SELECT on. We also give DBQuery an array variable to store the returned data in, a variable to store the number of records returned and a variable to store the number of fields per record. So:

Let>SQL=SELECT * FROM CUSTOMERS
DBQuery>dbH,SQL,rsCustomers,numRecs,numFields

Here, rsCustomers is the array in which the data should be stored. The array takes the format:

rsCustomers_RECNUM_FIELDNUM

So, let’s say the above DBQuery returns a recordset containing two records, each with three fields, we’d end up with:

rsCustomers_1_1
rsCustomers_1_2
rsCustomers_1_3
rsCustomers_2_1
rsCustomers_2_2
rsCustomers_2_3

numRecs tells us the number of records returned and numFields tells us how many fields there are.

We could loop through every field with:

Let>r=0
Repeat>r
  Let>r=r+1
  Let>f=0
  Repeat>f
    Let>f=f+1
    Let>this_field=rsCustomers_%r%_%f%
    Message>this_field
  Until>f=numFields
Until>r=numRecs

Modifying Data

To perform any SQL statement that does not return data use the DBExec command. E.g. DBExec can be used for a DELETE, INSERT or UPDATE query. DBExec again takes a database reference returned by DBConnect, the SQL statement, and returns the number of rows affected:

Let>SQL=DELETE FROM CUSTOMERS WHERE CUSTID=1532
DBExec>dbH,SQL,rowsAffected

In this example rowsAffected will contain the number of rows that were deleted.

If it doesn’t work it could be that your lovely database administrator may not have given you DELETE privileges. He’s probably worried you’re going to try something like this:

Let>SQL=DELETE * FROM CUSTOMERS
DBExec>dbH,SQL,rowsAffected

Closing the Database

Just as you shouldn’t leave doors open after you, you really ought to close any connection to the database also. Do this with DBClose which just wants the database reference returned by DBConnect:

DBClose>dbH

And that’s pretty much it.

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.

Common questions we receive are things like “What’s the best way to learn more about Macro Scheduler?”, or “How can I advance my Macro Scheduler skills?”. You may be just getting started, or you may be wondering whether you can improve one of your scripts or how to tackle another process. There are a number of resources available.

Scripting Windows for Beginners

I recommend that new users start by reading the “Scripting Windows for Beginners” guide in the help file. This runs through a simple process of automating Windows Notepad. It only takes a few minutes but it introduces the key concepts of automating with Macro Scheduler. All the commands you learn in that tutorial will be needed in almost any other automation process. So do take the time to work through it.

How to Start Writing an Automation Script

Another useful resource for those just getting started is this article. I believe that if you know the process you are automating, and use some good old paper and a pen to jot down each key send and note down titles of windows as they appear, and any other timings/events that are important, you’ll be half way to creating your script. Read the article to learn more.

Getting more Advanced

The software comes with a number of example scripts showing how to get text from the screen, how to use screen image recognition to find and click on objects, how to read data from Excel, how to simulate user input, etc. Look at these examples and try to understand how they work. You’ll also find examples in the forums.

Articles on Specific Topics

Working with databases
Working with Excel
Screen Image Recognition
Text Capture

Some of these have downloads and videos with them. Browse/Search the Blog for more articles. There’s also the FAQs and other Support Resources. I’m adding articles all the time to the blog, so keep an eye on it and/or subscribe to the RSS feed, or subscribe by email. If there is something you’d like me to write about which hasn’t yet been covered, please let me know.

Search for Solutions

Search the forums here
Search the blog articles at the top right of any blog page.

Or search the entire site with Google: http://www.mjtnet.com/search.htm

Learning on the Job

Everyone learns differently. I prefer to learn by doing. There are usually no rights or wrongs in software automation. The right solution is the one that works and the one that you are comfortable with. So for anyone tackling a particular automation scenario for the first time I say just get stuck in. Break the process down to chunks and tackle one chunk at a time. If you’re unsure about anything ask in the forums, email us, or give us a call. But don’t be worried about having a go.

One on One Consultations

If you’re the sort of person who would prefer some one on one tuition, no problem. We can arrange a desktop sharing session. I’m not a believer in too many contrived “hello world” type learning examples and since one person’s automation scenario can vary so wildly from someone else’s I don’t believe it’s possible to create a generic course. Instead I think it’s far better to look at your specific task and discuss how we might go about automating it. Most people find that after picking our brains for a few minutes they have what they need to get the job done. We can show you a few ideas and run through some of the code if needed. And more often than not this gets people going in the right direction to finish the task off.

The purpose of embedding a file into a Macro Scheduler script is to be able to have a script use that file on a computer that might not have the file or that the file is in an unknown location. For example a spreadsheet or database file could be embedded so that the script could work with data on a computer that does not have other access to the data. Or a custom dynamic link library (DLL) file or an executable file could be embedded to provide functionality that would not normally be found on all computers.

Though it’s not necessary to understand file construction to be able to embed files into Macro Scheduler scripts, the following discussion may help you understand what is going on in the process. This discussion may only apply to Microsoft Windows operating systems and the English keyboard.

All files are constructed from bytes. There are 256 unique 8 bit bytes sometimes referred to as octets. If you feel the need to investigate, Google the word “byte” and you will find enough information to keep you busy studying for years. ASCII (American Standard Code for Information Interchange) represents each of these 256 bytes with a unique character symbol and an integer from 0 to 255. Some of these characters are found on your keyboard. For example, if you open Notepad, then press the “A” key with your caps lock on then save the work to a file. That file will contain the byte represented in ASCII as an “A” character. The integer for ASCII character “A” is 65. The binary number, which is how the computer sees the character, is 01000001. Notice that there are 8 characters in the binary representation, that is why they are referred to as octets. To reiterate, ALL files are constructed from combinations of 256 8 bit bytes.

With a few inclusions the ASCII characters in the range from 32 through 127 are generally referred to as “text” characters. These are the characters that you can type from your keyboard. There are three notable inclusions. ASCII character number 9 is a Tab. ASCII character number 10 is a line feed and ASCII character number 13 is a carriage return. You will send ASCII characters 13 and 10, in that order, whenever you press the enter key while editing a text document.

Macro Scheduler scripts are “text” files. This means that scripts should only contain the ASCII characters in the range from 32 through 127 and the notable inclusions. Since most file types are not “text” files, they will include most or all of the 256 ASCII characters. The challenge is how to embed a non-text file within a text only Macro Scheduler script. I know of three easy answers.

In ASCII every character has a representative number. Numbers are text characters. VBScript, which is functional within Macro Scheduler scripts, can convert file bytes to ASCII numbers or ASCII numbers to file bytes.

A second way to represent bytes is by the hexidecimal equivalent of the ASCII number. Google “hexidecimal” for more information on hexidecimal (often shortened to hex). Like ASCII, hexidecimal is a text representation of each of the 256 bytes from which files are constructed. VBScript can be used to convert file bytes from hexidecimal and hexidecimal to file bytes.

A third way to represent file bytes as text is by using Base64. If you want a detailed explanation of base64 look here: http://en.wikipedia.org/wiki/Base64
Base64 represents ASCII bytes as text by grouping file bytes by threes and representing the group with a unique four character text name.

Base64 generally uses less space than ASCII or hex. ASCII uses from 1 to 3 characters to represent each character, plus you need a delimiter because you wouldn’t otherwise know where each character description began and ended. So you have a minimum of 2 characters per file byte and in most cases 4 characters per file byte. Hex is more efficient since it uses exactly 2 characters to represent each file byte and therefore no delimiter is needed. Base64 is better still because its technique only adds about 30-40% more characters than the original text and no delimiter is needed. The following example shows the efficiencies using the phrase “A quick brown fox”.

Text: A quick brown fox
17 characters

Base64: QSBxdWljayBicm93biBmb3g=
24 characters

Hex: 4120717569636B2062726F776E20666F78
34 characters

ASCII: 65 32 113 117 105 99 107 32 98 114 111 119 110 32 102 111 120
61 characters (Spaces count)

Base64 has one more huge advantage when it comes to embedding files in Macro Scheduler scripts, Base64 encoding and decoding is built into Macro Scheduler. One line of Macro Scheduler code will encode or decode a file rather than 20 lines or more to encode or decode to ASCII numbers or hex using VBScript.

Here is a process we can use to embed a base64 encoded file into a script.

The first step is to create a new script. For convenience you might call it “Base64 to clipboard”. This new script will contain the following lines.

Input>filename,Browse to Select a file for Base64 encoding
ReadFile>filename,filedata
Base64>filedata,Encode,b64data
PutClipBoard>b64data

Running this script will encode the selected file’s contents to base64 and place the base64 encoding onto the clipboard.

The next step is to get the base64 encoding into a script. Open a script in the advanced editor, place the cursor in an appropriate location and type:

Let>SomeVariable=

At the end of that line press Ctrl+C to paste the base64 encoding into the script.

We have now, encoded a file to base64 and placed the encoded file text into the script and assigned the text to a variable. The next step is to write the text back out to a file that will be used by the script. To do that we use the WriteLn> function. One very useful feature of the WriteLn> function is that by default, it adds the carriage return and line feed characters on the end of each line it writes. Unfortunately, those characters didn’t exist at the end of the original file and if we add those characters to the file we are creating, the file will be corrupt. Fortunately we can disable the default behavior by simply setting the Macro Scheduler system variable WLN_NOCRLF to 1. The next two lines will properly create our file.

Base64>SomeVariable,Decode,BinaryData
Let>WLN_NOCRLF=1
WriteLn>[Path]\[FileToCreate],wres,BinaryData

“[Path]\[FileToCreate]” is, of course, the path and file that you want the script to write for later use. “wres” is the result variable required by the WriteLn syntax. “BinaryData” is the variable that contains the Base64 decoded file information. You might want to use %TEMP_DIR% or %SCRIPT_DIR% in place of [Path] to have the file saved to the temp folder, or in the same location as the script.

The file is now available for use by the script.

Good practice dictates that we would place lines in the script to delete the file as the script is closing. And to be safe, I like to check to see if the file exists as the script is opening and if it exists, delete it there as well. So I would place the following lines at the start and at the end of a script.

IfFileExists>[Path]\\\[FileToCreate]
  DeleteFile>[Path]\[FileToCreate]
Endif

___________
Dick Lockey is M.I.S. Manager at Iowa Laser Technology, Inc., and has been using Macro Scheduler in his work since 2001. He is a regular contributor to the Macro Scheduler forums.

Most of the time when we are automating a process we are able to predict the sequence of events. We are working with a deterministic process and a linear flow of actions. But there are occasions when things can happen during a process that we cannot predict precisely. E.g.:

• We might know that a dialog or window may appear sometime during the process, but we cannot predict exactly when that will happen.
• We may have a situation where after entering some data into a text field a dialog may, or may not appear.
• There might be some other software running on the PC which randomly pops up an error box. And we need a way to clear that when it happens.

There are a number of ways we can deal with such situations.

Window Event Schedules

If you have a situation where a known window can randomly appear - say a known error box - which always has the same window title, the simplest approach is to use the Window Event schedule in the Advanced Scheduling properties. Simply create a macro which closes the box - perhaps all it has to do is press enter - and specify the window title under Advanced Options in the macro properties. Then whenever Macro Scheduler sees this window it will run the macro and clear it.

Synchronous Coding

In the case where a window may, or may not appear after entering some data into a field, say a data validation dialog, we could just deal with this after sending the text, in regular fashion - something like:

Send>the_data
Wait>0.5
IfWindowOpen>Verification Alert
  Press Enter
Endif

So we simply send the data then IF the verification window appears, close it. But what if you have hundreds of data fields to enter? Dealing with each one would involve a lot of extra code.

OnEvent Window Event Handlers

Another way is to use the OnEvent function to create an event handler in your main script. There are three types of window events that can be monitored with OnEvent:

• WINDOW_OPEN - monitors a specific known window title, or window title substring
• WINDOW_NOTOPEN - fires the event handler when specified window closes
• WINDOW_NEWACTIVE - fires the event handler when there’s a new foreground window

OnEvent is used to create an “event handler” which is just a subroutine which will be executed whenever the event occurs. So, for example, using OnEvent you can tell the script to run a subroutine whenever a specified window appears, whenever that may be, while the rest of the script is executing.

So let’s say we are working with an application which could, at any time, pop up a warning box titled “Connection Error”, and this can be cleared just by pressing enter to hit the default OK button:

OnEvent>WINDOW_OPEN,Connection Error,2,CloseWarning

..
.. rest of script here
.. 

SRT>CloseWarning
  Press Enter
End>CloseWarning

Of course there are a whole load of other things you can do. We may have a window whose title is always the same but the content differs and we need to react according to the content. In this case our event handler subroutine would have extra code in it to determine which type of dialog it is. We might do this using the text capture functions to read the text from the dialog, or using Screen Image Recognition to check for the presence of an object.

Maintaining Focus

Here’s an idea for an event handler which ensures the target application is always focused. If another application should steal focus at any point during the running of the script, it just grabs focus back again. It’s always good advice to use SetFocus before sending text. But if you have thousands of Send commands and want to slim down your script and make it more readable you could use this approach. Anyway, it’s just an example:

.. your code here to start and focus the app you want to automate, e.g.:
Run>Notepad.exe
WaitWindowOpen>Untitled - Notepad

//assuming the target window is now focused, get it's handle and process name
Let>WIN_USEHANDLE=1
GetActiveWindow>MyWindowHandle,x,y
GetWindowProcess>MyWindowHandle,pid,MyProcessName
Let>WIN_USEHANDLE=0

//now set up the event that is fired when a new window appears
OnEvent>WINDOW_NEWACTIVE,0,0,HandleNewWindow

..
..
.. rest of script here
..
..

//When a new window that does not belong to our process appears,
// set focus back to our window
SRT>HandleNewWindow
  Let>WIN_USEHANDLE=1
  GetActiveWindow>hwnd,x,y
  GetWindowProcess>hwnd,pid,winProcName
  If>winProcName<>MyProcessName
     SetFocus>MyWindowHandle
  Endif
  Let>WIN_USEHANDLE=0
End>HandleNewWindow

Note how this code gets the window handle and process name of your target window. Then whenever a new window appears the HandleNewWindow subroutine is fired which gets the process name of the active window. If the process name of the new active window is not the process name of your target window (i.e. the new window belongs to some other application) it sets focus back to your original window.

I hope this gives you a useful introduction to OnEvent event handlers and how they can be used to run code at any point during the script in response to events. OnEvent can also be used to detect files, dialog events, dialog changes and keyboard and mouse actions. For further information please see OnEvent in the help file.