Marcus' Macro Scheduler Blog

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.

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.

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.

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.

In this post I discussed the Text Capture commands and explained what kind of text can be captured.

To try out the text capture functionality launch the latest version of Macro Scheduler. Click New to create a new script and then in the Code Builder locate the “Text Capture Wizard”. Point the mouse at some text while holding the Shift key down.

You can see a video of this in action here.

This Wizard will let you determine whether or not the text you want to capture can be captured with the Text Capture commands. If it is not revealed when you move the mouse over it while holding Shift down then it must not be generated by Windows text out functions. See my previous post for an explanation.

As I have mentioned previously, Macro Scheduler 10 introduces some powerful new commands for capturing screen text. In this post I aim to explain what kinds of text can be captured with these new commands and why there will always be some text that cannot be retrieved.

First let’s look at how the existing functions, GetWindowText and GetObjectText work in Macro Scheduler 9.x and below.

Open up Macro Scheduler and click on the Tools menu and then the “View System Windows” option. You’ll end up with a window that looks something like this:

What we are looking at is a tree representation of windows open on the system. In the above screen shot the highlighted line is showing us an object of class “Button” with caption “Test Center”. Each line gives us the current handle of the object, followed by its class name and then its caption text, if any.

This caption text belonging to an object is made available to other processes – it is published if you will. An app can simply ask the control for its text by sending a simple message to it. That is what Macro Scheduler is doing when it builds this list of windows and objects. Macro Scheduler enumerates all top level windows and then for each one enumerates each of its child “windows”. Note that I use the term window interchangeably with object or control here – the controls that appear in the list are “windowed controls” – they have window handles. A handle allows us to interact with the control. If we know its handle we can send a message to it saying “please give me your text”. And so we get the text of the control back. This is what GetObjectText and GetWindowText do.

There are a number of shortfalls to this approach. One is that the “caption text” that the object publishes is not always the text that you see on the screen. In the case of standard Buttons, Edits, Windows and Checkboxes the published text is usually what you see. But other objects don’t necessarily work the same way. We usually know where we are with common controls – ones that belong to Windows, but custom controls in third party software may not follow the same rules. And a treeview’s caption, for example, is not the text belonging to all its nodes which is written to the screen. Furthermore not all text belongs to windowed controls. In Delphi applications, a control class called TLabel is commonly used as a way to write text on a window. These are often used to label other controls like edit boxes. But TLabels are not windowed controls – they don’t have handles. So this technique will not be able to retrieve their text.

We also can’t use this approach to get text from the likes of Word documents or Internet Explorer pages. This text is not just some simple caption property belonging to an ordinary control – it is created in a more direct way.

When Windows writes text to the screen it uses one of a number of functions deep within the Windows API. Most Windows applications will trigger these functions whether or not the programmer realises it. One such function is TextOut:

Windows GDI – TextOut
The TextOut function writes a character string at the specified location, using the currently selected font, background color, and text color.

Note that this function is part of gdi32 which is responsible for graphics – GDI = Graphics Device Interface. So TextOut is being called to “paint” a character to the screen.

With Macro Scheduler 10, when you call one of the new text capture commands Macro Scheduler uses a “hook” to listen in to calls to TextOut and other similar functions. It is therefore able to intercept what is written to the screen and retrieve the text output by a window.

This works with all kinds of applications including Microsoft Office, Internet Explorer, Firefox and the vast majority of everything else. There are still some exceptions though. Remember that this works by hooking these low level functions within Windows that are used to create text. The vast majority of Windows applications will use these system calls (often indirectly). However, some software may not. There’s no reason why a programmer can’t write text in an even lower level way – he might decide to paint a word pixel by pixel.

As an example – Java applications written with the AWT or SWT frameworks write text using Windows API functions. So we can detect text from those. But if you have a Java app produced with the Swing libraries, which handle text output their own way, you’re not going to be able to capture the text from it.

Finally, what about text on images? Well, text on an image was already there. It was painted by the artist. It is set in stone. So text that appears in a jpg, bmp or any other image file, cannot be detected with the new text capture commands, because it isn’t produced on the fly by one of Windows own text output functions.

The best way to determine whether or not the text you are seeing can be captured with the new text capture commands is to fire up the “Text Capture” sample macro. This will show you the text beneath your mouse cursor. So move the mouse over the text you are interested in and see if it gets displayed in the dialog. If it is, you know you can use the text capture commands to retrieve this text in your macro.

The only way to detect text that cannot be detected with the text capture commands is via OCR. Two methods to do this in Macro Scheduler are discussed here and here.

Something that comes up now and then is whether or not compiled Macro Scheduler macros can respond to hot-keys. Macro Scheduler itself lets you assign system-wide hot-keys to macros so that when you hit that key combination, no matter where you are in Windows, the macro will be fired.

Some people want to be able to distribute compiled macros (EXEs) to their friends and colleagues and have those EXEs respond to hot-keys in the same way. Something has to be running in order to listen for the hot-key and act on it. In the case of regular old Macro Scheduler that is Macro Scheduler itself. So how do we achieve it with a compiled macro?

Method 1 – make the EXE itself listen for the hot-key:

Use OnEvent to create a KEY_DOWN event handler to watch for the hot-key sequence:

OnEvent>KEY_DOWN,G,5,DoMacro

Label>Idle_Loop
Wait>0.02
Goto>Idle_Loop

SRT>DoMacro
..
.. Your Code Here
..
End>DoMacro

The above will run indefinitely and whenever CTRL+ALT+G is pressed the macro code in the DoMacro subroutine will be fired. Clearly the macro has to be running all the time to listen for the hotkey – after all, something has to listen for it. So you could have the EXE run on startup by adding a shortcut to the Startup folder for example. Perhaps also compile the EXE with the /NOSYSTRAY and /HIDE parms so that the EXE is not visible when it runs. Or, better, compile with a custom icon so that your EXE has it’s own fancy icon in the task bar.

See the documentation for the OnEvent function for more info on how to trap different keystrokes.

Method 2 – Let Windows do the work:

Don’t tell me – all these years using Microsoft Windows and you didn’t realise that keyboard shortcuts can be assigned to shortcuts?

So just create a desktop shortcut to your compiled EXE. Right click on the desktop shortcut and select properties. Notice the “Shortcut key” field. Press the keys you want to use to trigger the EXE. Press Ok. Sorted.