Marcus' Macro Scheduler Blog

Macro Scheduler comes complete with some native functions for controlling Excel, such as XLOpen, XLGetCell, XLSetCell and others. Obviously, although we intend to add more functions over time, not every possible Excel function has been duplicated. So sometimes you may want to utilise COM via VBScript which allows you to access the entire Excel API. There are plenty of examples of this here in the blog and on the forums.

But what if you want to use a combination of both? You might already have a script which uses the native XL functions to open a sheet and get or set some data. Let’s say you now want to augment this with an Excel method which is not exposed by the native functions. Rather than re-writing your entire script to use VBScript, is there a way we can let VBScript take over?

While it’s not possible to share native XL references with VBScript object references, what we can do is have VBScript attach to an open instance of Excel using the GetObject function. So sometime after running XLOpen we could then run a VBScript function which does a GetObject to get an object reference to Excel and then after that we are able to utlise any Excel function we like via VBScript.

The following script demonstrates:

VBSTART
  Dim xlApp
  Dim xlBook
  Sub GetXL
    Set xlApp = GetObject(,"Excel.Application")
    Set xlBook = xlApp.ActiveWorkbook
  End Sub

  Function FindCell(Sheet,Data)
    Dim theCell
    Dim xlValues
    xlValues = -4163

    Dim xlSheet
    Set xlSheet = xlBook.Worksheets(Sheet)
    xlSheet.Range("A1").Select
    Set theCell = xlSheet.Cells.Find(Data, xlApp.ActiveCell, xlValues)
    FindCell = CStr(theCell.Row) & ":" & CStr(theCell.Column)
  End Function
VBEND

//Open an XLS file natively
XLOpen>%SCRIPT_DIR%\example.xls,1,xlH

//Call GetXL to give VBScript a reference to the XL instance
VBRun>GetXL

//now we can access any XL function via VBScript
VBEval>FindCell("Sheet1","Price"),res

The only thing to be careful of is that there are no existing copies of Excel open before the one opened by XLOpen because according to the Microsoft docs GetObject will attach to the first opened instance. You could of course make the script check for this.

In the comments to my last post Richard asked how we can wait for a process to finish updating a file.

In my reply to Richard I point out that most of the time we can use a visual cue. Where possible this is the approach I would use. Usually an application would present some kind of visual cue when the file operation finishes. A dialog box might appear, or disappear, an object may become enabled or disabled, some text may appear on the screen, etc. We might be able to use commands such as WaitWindowOpen, WaitWindowClosed, WaitWindowChanged, WaitScreenText, WaitScreenImage, WaitCursorChanged, WaitPixelColor, or WaitRectChanged. Or we might even create our own wait loop which checks for some value or property. I mention this approach in Top Tips for Reliable Macros.

Unfortunately there are sometimes scenarios where there is no visual cue and we have nothing we can “see” which tells us the file operation has completed.

If the file is locked by the application that is writing to it, we could create a wait loop which checks to see if the file is still locked or not.

How might we achieve that? Well, why not just try to read from the file. If the file is locked we’re not going to be able to read it right? So we could just do:

Label>wait_file
ReadLn>file,1,res
Pos>##ERR##,res,1,pErr
If>pErr>0
  Wait>0.2
  Goto>wait_file
Endif

This will work for any kind of file.

Of course this assumes that the file is locked for reading by other processes.

It’s possible that the application locks the file only for writing. In that situation we could use WriteLn to attempt to write an empty string to the file:

Let>WLN_NOCRLF=1
Label>wait_file
WriteLn>file,res,{""}
If>RES<>0
  Wait>0.2
  Goto>wait_file
Endif

But that’s a bit invasive in the instance that the file IS available as although we’re writing nothing, we’re still modifying it so updating it’s timestamp.

We can get a bit more control over this with VBScript where we can attempt to open a file for appending without actually modifying it:

VBSTART
Function TestFile(filename)
   Set oFSO = CreateObject("Scripting.FileSystemObject") 
   On Error Resume Next 
   Set f = oFSO.OpenTextFile(filename, 8, True)
   TestFile = Err.Number 
End Function
VBEND

Let>file=C:\files\bmp.bmp

Label>wait_file
VBEval>TestFile("%file%"),fRes
If>fRes<>0
  Wait>0.2
  Goto>wait_file
Endif

All the TestFile function does is try to open the file for appending, and if an error occurs it returns the error code. So if we get zero back, there’s no error and the file must be available for writing and therefore not locked.

Don’t be put off by the fact that this uses the OpenTextFile method. It will still work with any kind of file. We’re not actually going to attempt to modify it. We’re just trying to see if we can open it. So the file type is unimportant.

Note that in these samples I haven’t checked for any specific error code nor have I checked for the existence of the file first – I’m assuming any error means the file isn’t available. An error will also be returned if the file does not exist, so you’d probably want to use IfFileExists first. You might even want to put FileExists in a loop so that you can wait until it exists.

Note also the small wait in each loop. This is just to make sure the loop isn’t so tight that it hogs CPU. Better to be nice to the system and give other processes a chance to breathe.

Today I was helping someone who was wanting to write a script to take screen-shots from one application and then paste those screen-shots into Microsoft Excel.

Initially things weren’t working reliably because the script didn’t factor in the time taken for the large bitmap of a screen-shot to exist in the clipboard after pressing the print screen button, before attempting to paste into Excel. E.g. consider:

Press Print Screen
SetFocus>Microsoft - Excel*
Press CTRL
Send>v
Release CTRL

The above is probably going to fail most of the time because a screen shot is a large bitmap and is going to take some time to arrive on the clipboard, but the script above performs a paste in Excel immediately after pressing print screen. The print screen key being depressed and the clipboard containing the bitmap are not the same thing.

While we could have just said “wait 5 seconds” and that would probably have been fine for evermore, it isn’t very sensitive and wouldn’t be ideal for a script that needs to run as fast as possible. Ideally we only want to wait until we know the bitmap is in the clipboard.

Text is usually smaller than a bitmap, but for large text items one way to make things bulletproof is to do something like this:

PutClipBoard>dummy

SetFocus>source_app_title
Press CTRL
Send>c
Release CTRL

Label>wait_for_data
Wait>0.2
GetClipBoard>clipdata
If>clipdata=dummy
  Goto>wait_for_data
Endif

By putting a known value onto the clipboard in the first place we can then have a little loop which keeps checking the clipboard until the returned value is not our known value. We then know our CTRL-V has worked and that we can safely paste to the target application.

But how can we do the same thing when the clipboard data is an image? The above won’t work because GetClipBoard won’t return anything for non-textual data.

Well, Windows has a function called IsClipboardFormatAvailable which will allow us to determine what kind of data is on the clipboard. So we could use this in a similar way to above to see if the clipboard contains a bitmap or not. Like this:

Let>CF_BITMAP=2

Let>haveBMP=0
PutClipBoard>dummy

Press Print Screen

While>haveBMP=0
  LibFunc>user32,IsClipboardFormatAvailable,haveBMP,CF_BITMAP
  Wait>0.2
EndWhile

We could then paste it somewhere:

SetFocus>Document - WordPad
Press CTRL
Send>v
Release CTRL

It’s always nice to wait only as long as we have to and it makes the script more reliable and portable.

Edit: 23 March 2011. In Version 12 it is possible to make a dialog stay on top simply by setting the dialog’s FormStyle property to fsStayOnTop. The method outlined in this post is not necessary for v12 dialogs but will remain as it can be used for older versions and the same approach can be used for windows belonging to other processes.

A question that comes up every now and then is how to make a custom dialog stay “on top” of other windows even when it loses the focus. There is a way to do this by calling the Windows API function SetWindowPos. There are examples in the forums but for convenience here’s a version of one:

SRT>StayOnTop
  Let>HWND_TOPMOST=-1
  Let>HWND_NOTOPMOST=-2
  Let>SWP_NOSIZE=1
  Let>SWP_NOMOVE=2
  Let>SWP_NOACTIVATE=16
  Let>SWP_SHOWWINDOW=64
  Let>WindowHandle=%StayOnTop_var_1%
  Let>Flags={%SWP_NOACTIVATE% Or %SWP_SHOWWINDOW% Or %SWP_NOMOVE% Or %SWP_NOSIZE%}
  LibFunc>User32,SetWindowPos,swpr,%WindowHandle%,HWND_TOPMOST,0,0,0,0,Flags
END>StayOnTop

This subroutine takes a window handle and modifies the properties of that window to make it stay above other windows.

For example, if we had just opened Notepad and wanted to force it to stay on top we could do:

GetWindowHandle>Untitled - Notepad,hwndNotepad
GoSub>StayOnTop,hwndNotepad

Or to get the handle of the active window use GetActiveWindow with WIN_USEHANDLE set to 1.

To set one of your own custom dialogs to stay on top use:

GoSub>StayOnTop,DialogName.Handle

Don’t forget that we are at the mercy of Windows here (and possibly the developers of the app we’re trying to force to the top). It’s a bit rude to have a window floating around on top of other windows. And consider what would happen if another window is opened which is also set to stay on top in the same way? You can’t have two windows on top. So multiple “stay on top” windows will still overlap each other depending on which one has the focus. In short you can’t really guarantee that a window will always be on top.

Macro Scheduler has a number of scheduling features to allow you to specify when a macro should fire. One of these mechanisms is called a Trigger. There are several trigger types:

• Window Event
• File Event
• Folder Event
• Custom Event

A Window Event can be set to fire when a specified window appears or disappears. Similarly file events can be set to fire the macro when the specified file exists or ceases to exist. Folder events offer much the same (a folder exists or ceases to exist) but also a little more. A Folder event can also be configured which will fire the macro when a new file appears in the folder, or a file is removed from the folder.

New File Triggers

With a “New File in Folder” trigger the macro will fire whenever a new file appears in the folder, regardless of what that file is called. This can be very useful and is often used to detect incoming files for further processing. In a situation like this we would need to detect that a new file has appeared and then do something with it. Using the New File in Folder trigger will cause the macro to run, but then we need the macro to determine what that file is called. We can do that with a VBScript function which returns the newest file in a folder:

VBSTART
Function NewestFile(Folder)
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(Folder)
dPrevDate = "0"
For Each oFile In oFolder.Files
  If DateDiff("s", dPrevDate, oFile.DateLastModified) > 0 Then
    sNewestFile = oFile.Path
    dPrevDate = oFile.DateLastModified
  End If
Next
NewestFile = sNewestFile
End Function
VBEND

We can then call it like this:

VBEval>NewestFile("c:\downloads"),filename

This will return in filename the path of the newest file in the given folder. So if our macro is fired by a new file appearing in a folder it can then run this code to get that file’s path and do something with it.

Custom Triggers

Custom triggers allow you to create any kind of trigger you can think of by executing Macro Scheduler code. Maybe you want to monitor the contents of a text file or an INI file entry, a registry entry or the size of a specific file. If you can code the check in Macro Scheduler you can create a trigger out of it.

Custom triggers work by linking to a script (.scp) file which contain two subroutines. One subroutine is called Trigger and the other Reset. Initially the Trigger subroutine is called repeatedly until it sets MACRO_RESULT to TRUE. Thereupon Macro Scheduler calls the Reset routine instead until it too sets MACRO_RESULT to TRUE and the cycle then continues.

By way of an example lets say we want to trigger a macro based on the value of an INI file entry. Let’s say our INI file looks like this:

[MyStuff]
Color=red

Let’s say we want to trigger a macro to run when the Color entry in the INI file gets set to “blue”. Here’s our trigger script:

SRT>Trigger
  ReadIniFile>d:\files\myini.ini,MyStuff,Color,gColor
  If>gColor=blue
    Let>MACRO_RESULT=TRUE
  Endif
END>Trigger

SRT>Reset
  ReadIniFile>d:\files\myini.ini,MyStuff,Color,gColor
  If>gColor<>blue
    Let>MACRO_RESULT=TRUE
  Endif
END>Reset

So here the Trigger routine which is executed continually by the scheduler will return TRUE only if Color in the INI file becomes equal to “blue”. Thereupon the Reset routine will be executed which will reset the trigger when Color is no longer “blue”. After that the Trigger routine is executed again.

Since Trigger routines are run frequently on a very tight interval they should be kept as small as possible and should not be long running. Don’t make Trigger scripts that perform too much complicated processing or include delays as this could cause resource issues. Sometimes, for more complicated “triggers” or monitoring scripts you may be better off creating a looping macro or repeating macro. E.g. if you wanted to poll a POP3 server for an email message containing specific content this is a more time consuming process subject to network delays so would probably not be a good candidate for a Custom Trigger. Instead create a macro which checks the email every few minutes, or one that loops continuously.

When sending keystrokes to other applications remember that Macro Scheduler works much faster than a human being can type. Many applications do form field verification or background processing on the fly as the text is received. And most applications were designed on the assumption that a human being would be operating them. It may not have occurred to the developers that a robot might try to send a stream of text to the UI at the rate of 5000 characters a second!

With some applications if you try to send too many characters at once you may find that some of those characters fail to show up, or the string is truncated.

The solution should be obvious by now. That is to slow down the key send rate. You can do this easily by setting the SK_DELAY parameter which introduces a specified millisecond delay between each character.

Let>SK_DELAY=20
Send>long_string

Of course you could break the long string down into smaller chunks and insert a delay between them:

Send>part_1
Wait>0.3
Send>part_2
Wait>0.2
Send>part_3

But SK_DELAY is simpler and easier if you can’t control the length of the data easily.

A related issue I see every now and then is when “tabbing” from one field to another on a form. Pressing the tab key moves the focus to the next field, so we use this while sending data into a form. Sometimes we’ll encounter an application which needs a bit of time after a field has been focused before it can accept data, or the other way around. So sending Tab immediately after/before sending the data with no delay fails to work. Adding in a small delay between sending the data and the Tab solves it:

Send>field_data
Wait>0.3
Press Tab

Wait>0.3
Send>next_field
Wait>0.3
Press Tab

etc

Similarly when we need to tab through lots of fields at once without sending data we may try to save ourselves coding time by writing:

Press Tab * 10

But if the app needs time to react between each tab we may have to split that out into separate tabs with delays between them or replace with a repeat/until loop:

Let>tabs=0
Repeat>tabs
  Press Tab
  Wait>0.2
  Let>tabs=tabs+1
Until>tabs=10

As I’ve said many times before the most common causes of things not working as expected are either timing or focus. Macros run way faster than a human can type which is often beneficial but if things aren’t quite right start off by slowing the macro down a bit. You can always speed it up later!

A quick and easy way to launch a URL in the default web browser is just to use the ExecuteFile command:

ExecuteFile>http://www.mjtnet.com/

However, I have recently discovered that ExecuteFile is currently limited to a command line length of MAX_PATH (260 chars) and that if more than 260 chars are passed it will cause Macro Scheduler to crash. This is a bug that we’ll get fixed in the next update (increase the limit if possible AND prevent a crash if more chars than can be handled are passed).

Another way to launch a URL in the default browser is to use the Run command. First you need to determine the path to the default browser which you can do by querying the registry:

Let>url=http://www.mjtnet.com/

//get path of default browser ...
RegistryReadKey>HKEY_CLASSES_ROOT,htmlfile\shell\open\command,,browserEXE

Run>%browserEXE% %url%