Marcus' Macro Scheduler Blog

I just posted a script to the Scripts ‘n Tips forum with a function to perform an HTTP file upload.

Macro Scheduler‘s LibFunc command allows you to run functions contained inside DLLs.  A DLL, or Dynamic Link Library, is a file which contains functions that other programs can use.  Windows includes a number of DLLs containing lots of functions that make Windows tick, and other applications are able to call them.  These functions are known as the Windows API (Application Programming Interface). Using LibFunc a Macro Scheduler script can access some of these functions too.

The Windows API Reference can be found here:
http://msdn.microsoft.com/en-us/library/aa383749(VS.85).aspx

This provides a list of functions which you can browse alphabetically or by category.

Data Types

Before I go on I should mention data types.  Not every Windows API function can be used by Macro Scheduler.  This is because Macro Scheduler does not know about every possible Windows data type.  Macro Scheduler currently only knows about integers, long integers and strings. Almost any function that requires or returns integer and/or character based data can be called.  But anything that requires, for example, a record structure or a callback function can not be used.

The API documentation lists the Windows data types here:
http://msdn.microsoft.com/en-us/library/aa383751(VS.85).aspx

Many of these are based on the same fundamental data types.  E.g. all the HANDLE types are just unsigned numbers, so are supported by Macro Scheduler’s long integer.  LPTSTR and LPCTSTR are interchangeable with strings. From this list only CALLBACK and FLOAT are NOT compatible.

So, as long as the function requires or returns only bools, integers or strings, we should be able to use the function in Macro Scheduler.  Note that BOOLs are really just integers.  A BOOL is an integer with value 1 (true) or 0 (false).

An Example: CopyFile

Let’s look at a Windows API function and how we can use it in Macro Scheduler.

Take a look at the CopyFile function:
http://msdn.microsoft.com/en-us/library/aa363851(VS.85).aspx

At the top of the page we are told what this function does:

“Copies an existing file to a new file.”

We are then given the syntax.  You’ll notice that it is provided using C++ syntax.  It certainly helps if you know C++ but it is not essential and hopefully this example will help you understand what the syntax definition is telling us:

BOOL WINAPI CopyFile(
  __in  LPCTSTR lpExistingFileName,
  __in  LPCTSTR lpNewFileName,
  __in  BOOL bFailIfExists
);

The first thing this tells us is that CopyFile returns a BOOL (0 or 1).  Inside the parenthesis we see that the function requires three parameters.  The first two are of type LPCTSTR.  For our purposes this means it is a string.  The third parameter is a BOOL again.

We are then told what each parameter is for, what the function returns and various remarks.

While the names of the parameters are quite self explanatory the documentation gives us more detail.  So we can see that the first parameter lpExistingFileName represents the name of an existing file, lpNewFileName is what we set to the name of the new file we want to copy to, and bFailIfExists can be set to true (1) to make the function fail if the new file already exists or false (0) to overwrite.

We are told that the function returns zero if the function fails, or non zero if it succeeds.

DLL and Function Name

At the end of the page is some information crucial to us in the Requirements section.  This tells us which versions of the operating system support this function and what DLL it is contained in – Kernel32.dll in this case.  Note also that it tells us the alias names of the function.  In this case CopyFileA for the ANSI version and CopyFileW for the unicode version (Why “W” not “U” I hear you ask – W stands for WideString, a special form of string which can contain double byte characters).

So, putting it all together, we end up with the following LibFunc call:

LibFunc>kernel32.dll,CopyFileA,result,c:\source.txt,c:\my docs\new.txt,0

From left to right LibFunc takes the DLL name, then the function name, a variable which should return the result of the call and then the values to pass to the function. One thing to be aware of is that DLL function names are case sensitive. Make sure the function name is entered into the LibFunc command exactly as specified in the API documentation.

Try the above line with a real filename to see it in action.

Passing by Reference

Some DLL functions modify the values being passed to them.  Parameters are passed by reference rather than by value.  This means that what you’re really passing is a pointer to the memory address that stores that value, rather than just the value itself.  So when the function changes that value we can see the new value after the call. 

The way LibFunc handles this is that it puts each parameter value into an array, using the name of the return variable specified.  So if you specify the return value as “result” and the function takes 3 parameters LibFunc would return result, result_1, result_2, and result_3 where result contains the function result and result_1 to result_3 contain the values of the passed parameters which might have changed if the function modifies them.

Here’s an example of a Windows API function which returns data in a passed parameter:

http://msdn.microsoft.com/en-us/library/ms724373(VS.85).aspx

UINT WINAPI GetSystemDirectory(
  __out  LPTSTR lpBuffer,
  __in   UINT uSize
);

Note that the first parameter has the word “out” in front of it.  This signifies that its value is set by the function.  The function also returns an integer.  If we read the docs we see that the function writes the system directory in lpBuffer and returns the number of characters written to lpBuffer.

So I can use the following code to get the system directory:

LibFunc>Kernel32,GetSystemDirectoryA,dir,buffer,255
MidStr>dir_1,1,dir,sys_dir
MessageModal>System Directory: %sys_dir%

Note that I’ve set the return variable to “dir”. We can pass any old value in buffer, but I’ve used “buffer” here to make it obvious what it does.  Remember that LibFunc creates an array named after the result variable.  So we get “dir” containing the number of characters written to the buffer, dir_1 containing the buffer itself and dir_2 will just be 255 because that’s what we passed in but isn’t changed by the function as it is an “__in” parameter.

We set the maximum buffer size to 255.  So we need to extract only the characters returned, which is the reason why the function tells you how many characters it output.  So I’ve used MidStr to extract only those characters from the returned buffer.

Windows Constants

Many times we need to know the value of a Windows Constant.  The documentation for a function may refer to the name of a constant you need to use with the function.  E.g.:

ShowWindow
http://msdn.microsoft.com/en-us/library/ms633548(VS.85).aspx

BOOL ShowWindow(      
    HWND hWnd,
    int nCmdShow
)

The docs say that nCmdShow specifies how the window is to be shown and says it can be one of the following values: SW_FORCEMINIMIZE, SW_HIDE, SW_MAXIMIZE, SW_MINIMIZE, SW_RESTORE and so on.  These are Windows Constants.

In Windows development languages such as C++ and Delphi these constants are defined in the header files. In Macro Scheduler they are not defined, so we need to define them ourselves:

Let>SW_RESTORE=9

But, I hear you ask, how do I know that SW_RESTORE’s value is 9?  Well, if you have a development environment like Visual Studio or Delphi installed you can find out by searching the header files. 

However, if you don’t have this facility there’s a very handy free tool from Microsoft snappily titled “P/Invoke Interop Assistant” which contains a database of Windows functions and constants you can search.  You can download it from:

http://www.codeplex.com/clrinterop

Under the “SigImp Search” tab enter the constant you are looking for and it will tell you its value.

The Windows header files give the constant values in hexadecimal, so if obtaining them from the header files you will need to convert to decimal. The Windows Calculator is handy for doing this. “P/Invoke Interop Assistant” also shows the values in hexadecimal, but if you click the “Generate” button it will create C# or VB code with the value declared as an integer.

STDCALL Calling Convention

Finally, a note about calling conventions.  When a DLL is created the programmer can decide in what order parameters should be passed to the functions and who should clean up afterwards.  Windows API functions use the “stdcall” calling convention in which arguments are passed from right to left and the callee, i.e. the DLL, is responsible for cleaning the stack.  This therefore is the calling convention supported by LibFunc.  You don’t need to worry about this when calling Windows API functions.  But if you come to working with third party or custom DLLs, or ones you have created yourself, you will need to make sure the DLL uses the stdcall convention.

VBScript has a function called IsNumeric, but as discussed in this forum post VBScript numbers can contain the letter ‘E’ (and it would seem ‘D’ too). These are valid numbers as far as VBScript is concerned but in the real world we usually don’t care for them. Numbers may also be floats. So in the forum post we came up with the following VBScript functions for IsNumber and IsInteger:

VBSTART
Function IsNumber(var)
  IsNumber = (LCase(var) = UCase(var)) and isNumeric(var)
End Function

Function IsInteger(var)
  IsInteger = IsNumber(var) and (InStr(var,".") = 0)
End Function
VBEND

Now we have Easy Patterns I thought I’d show you another neat way to validate that a string is numeric:

//IsNumeric?
Let>data=154.3
RegEx>[lineStart][oneOrMore number or "."][lineEnd],data,1,matches,num_matches,0
If>num_matches>0
  //string IS numeric
Endif

If you just want to check the string is integer, change it to:

//IsInteger?
Let>data=154
RegEx>[lineStart][oneOrMore number][lineEnd],data,1,matches,num_matches,0
If>num_matches>0
  //string IS integer
Endif

If we want to see if a string merely contains a number we can do:

//Contains a number?
Let>data=fred 224 sally 4
RegEx>[oneOrMore number],data,1,matches,num_matches,0
If>num_matches>0
  //string CONTAINS number
Endif

An interesting discussion arose in the forums yesterday on how to read data from a CSV file. The usual suggestion is to use Separate to split each line into an array using the comma as the delimiter, as explained in my post here.

The problem, as highlighted in the forum topic, is: what if the line has a field which itself contains a comma? In CSV if a field contains a comma it will be encased in double quotes. E.g.:

Sally,”2,500″,Blue

If we just use Separate using the comma as a delimiter we will end up with the following values:

Sally
“2
500”
Blue

Of course this isn’t what we want. We want to end up with:

Sally
2,500
Blue

In the forum discussion a number of suggestions were made such as to use regular expressions to find the fields inside quotes and replace the commas with something else, then use Separate and then put the commas back. Certainly we could resort to some kind of text parsing and I’ve updated the post with a RegEx solution.

However, we can avoid all this by using DBQuery and an ADO connection string that lets us connect to and query a CSV file and treat it like a database table. All the work is done for us:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
DBQuery>dbH,select * from test.csv,fields,num_recs,num_fields

In the connection string specify the path of the CSV file in Data Source. We can also tell it whether or not the CSV file has a header line by setting HDR to NO or Yes.

This reads all the data from the CSV file into an array.

Here’s some code to read through the array and display each field one by one:

Let>rec=0
Repeat>rec
  Let>rec=rec+1
  Let>field=0
  Repeat>field
    Let>field=field+1
    Let>this_field=fields_%rec%_%field%
    MessageModal>this_field
  Until>field=num_fields
Until>rec=num_recs

If the CSV file has a header line change HDR to Yes and then we could also tell DBQuery to return the field names:

Let>ConStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents;Extended Properties='text;HDR=NO;FMT=Delimited'
DBConnect>ConStr,dbH
DBQuery>dbH,select * from test.csv,fields,num_recs,num_fields,1

Then we can access the data using the field names. E.g.:

Let>rec=0
Repeat>rec
  Let>rec=rec+1
  Let>name=fields_%rec%_name
  Let>number=fields_%rec%_number
  Let>colour=fields_%rec%_colour
  MessageModal>%name% %number% %colour%
Until>rec=num_recs

For more help with Macro Schedulers database functions see:
Using Macro Scheduler’s Database Functions