WINBATCH OLE PROGRAMMING

WINBATCH OLE PROGRAMMING - Part 3

by Stan Littlefield

Object Models

The previous sections were a Quick Start and framework for manipulating objects with Winbatch. This section looks at the structural nature of objects - the Object Model as it were - with sub-structures, properties, methods, events and collections. The treatment is far from exhaustive, but there are several coding examples and caveats which will enable you to prepare for coding with objects.

Much of the code you will be exposed to is similar to VBA. If you already know VBA, then you only need learn several nuances (some may call these restrictions) with Winbatch code. After some exposure, you will determine that coding itself is not the main issue - rather you need a good understanding of the Objects or Applications you wish to control. In the words of one VBA How To:

Learn what you need, when you need it. There is an almost overwhelming volume of material - develop skills in the use of a small fraction of this first and learn other parts as and when necessary.

My goal is to suggest, through example, how to effectively use Winbatch with objects. Many examples focus on products like Microsoft Office which dominate business desktops. First, I wish to point out what is (1) the relative ease with which Winbatch can work with objects and (2) common pitfalls and mistakes for the beginner:

Winbatch works with objects the same way as Visual Basic, or VBA ( the 'macro' language in Microsoft Office products). It is easy to translate existing 'code' in VB for insertion into a script to perform a particular task.

Often a Winbatch subroutine can very nearly resemble a VBA function. There are two minor syntactical differences. Winbatch normally requires () when calling an object method, while VBA doesn't - for example a method to Open a file in VBA might be Object.Open "filename", in Winbatch Object.Open("filename"). The second involves 'named parameters' - VBA might be written Object.GetIt param1:=something, param2:=else, while in Winbatch it would be Object.GetIt(:: param1=something, param2=else). Additionally, while VB supports a For Each construct, Winbatch uses ForEach.

While Winbatch does not support object 'events', improvements in error-handling allows you to trap and recover from even serious errors when coding for objects.

The Winbatch :wberrorhandler now includes a variable to collect and display 'Extended Error' information if available. This is particularly useful for instances when a script containing OLE code fails every 100th time. The Winbatch lasterror() might return a code of 1261 [Ole:Exception] - but the extended information might say something like "cannot perform this operation on a closed object". This can help you prepare for that 100th time.

Because Winbatch handles objects like VB, beginners often assume that Winbatch can replace VB code.

Just a caution to not confuse the likelihood of translating a code snippet with the fact of translating it. VB uses a 'Set' statement to declare something as a particular object type to which nothing corresponds in Winbatch. VB recognizes 'Nothing' as a way to close objects - i.e. Set Object = Nothing. Although 99% of the time Winbatch code can say Object = 0, there are exceptions, most notably disconnecting a Recordset (as we shall see later).

A common source of error and frustration involves creating inappropriate object references.

In theory, those who design objects do so in a hierarchical manner. A top object begets lower objects in the hierarchy (use of parent-child terminology is very popular in describing objects). The Excel hierarchy is one of the better documented examples. We look at it in some detail, but suffice to say if you make a valid reference for a Range Object without first instantiating a WorkSheet Object, your script blows up. In the final section on translating VB code, these pitfalls will again be addressed with more examples.

Let's look at another example using Microsoft Access. VB code to enumerate the tables in an Access database:

Set dbs = Application.CurrentData
    ' Search for open AccessObject objects in AllTables collection.
    For Each obj In dbs.AllTables
        Debug.Print obj.Name
    Next obj

And you write Winbatch code (using oA instead of Application)

dbs = oA.CurrentData
cVar = StrCat("[Tables]",@TAB)
ForEach obj In dbs.AllTables
   cVar = StrCat(cVar,obj.Name,@TAB)
Next

And this code works fine, but when you see

Set dbs = Application.CurrentData
    ' Search for open AccessObject objects in
    ' AllStoredProcedures collection.
    For Each obj In dbs.AllStoredProcedures
        Debug.Print obj.Name
    Next obj

Which you then translate, you get an error - Object Member Does Not Exist. This is because although both objects belong to the same parent, CurrentData, stored procedures are only available in an Access Project, not a database file (.mdb). It is important to get a thorough overview of a object model and hopefully avoid errors like above. The next paragraphs address this.

Starting with ODBC, Microsoft, to its credit, has crafted what is referred to as a Universal Data Access (UDA) strategy. The premise is that regardless how your data was created or stored, you should be able to get to it using a commonly accepted API. To use a common analogy: "How would you describe your Web page without mentioning its content? "

That would be the structure, or to simplify - an Object Model that contains aspects (known widely as methods and properties) which are open to a variety of programming languages, Winbatch being what we are interested in. In this respect 'data' becomes a widely variable concept - anything from an established database to a contacts folder in your email. For the purposes of programming, several of the larger components of the UDA can be described by an Object Mode. For example, Microsoft Excel has an object model - a hierarchy of all the objects that you can use in their VBA language. At the top of this hierarchy is the Application object, and all the other objects are below it.

MS Excel Object Model Sample 1

[shortened example]. And, if one were to look at the WorkSheet section of the Models, it enlarges to:

MS Excel Object Model Sample 2

Each of the items in the model, refer to aspects of Excel that make it possible to produce, modify, save, print etc� just as though you were doing it by sitting at the computer, opening Excel, and manually performing that particular task.

The dot operator (.) is used to navigate through the hierarchy. You connect objects with a dot to get to lesser objects. We are going to continue using the Excel Object since it is so well documented. In Winbatch:

oXL = CreateObject("Excel.Application")

creates a variable oXL that is a reference to the top Object in the hierarchy. If at some point you wished to change the font within a cell you essentially 'connect the dots' (start with the Application object, navigating to the workbook, worksheet, range and finally the font):

oXL.ActiveWorkbook.ActiveSheet.Range("A1").Font

Of course, although this one path to the Font object, you have not done anything - i.e. your code requires a reference to either get or set the Font for cell A1.

If oXL.ActiveWorkbook.ActiveSheet.Range("A1").Font<>"Tahoma" Then
oXL.ActiveWorkbook.ActiveSheet.Range("A1").Font = "Tahoma"

Objects have properties and methods. Properties are like characteristics of the objects. Methods are actions the object can perform. The Excel Object Model is fully documented in a Help File (For Office97 - an .hlp extension; for 2000 and greater a .chm extension). These are not always installed, but are available on the installation CD. [if you have Office 2000 or XP installed, search for vba*.chm]

MS Excel Object Model Sample 3 the Excel Model

Important: The VBAXL10.chm file does not teach how to use Excel.

Object Properties:

Several properties, each with several possible values are associated with every Object. Property values are generally data types of string/text, numeric, or Boolean (true/false). Looking at the Excel Application Object

MS Excel Object Model Sample 4

of particular interest is the 'visible' property. This holds a Boolean value and is initially set to 'False' meaning the Application Object is not visible when referenced with oXL = CreateObject("Excel.Application"). If you were proficient with programming objects this default state would allow you to perform all sorts of data manipulation within Excel without ever seeing it on the screen. However, if your program caused an error you would end up with an orphaned instance of Excel loaded on your computer that you cannot control, often requiring a reboot. Therefore until you are proficient it is best to code:

oXL = CreateObject("Excel.Application")
oXL.Visible = @TRUE

Properties are 'inter-object', i.e. the Visible property is associated with other objects in the Excel Model ( a worksheet can be visible/hidden, as can a Chart or elements of a Pivot Table ). I have included an Excel Properties script which you can practice with.

As of this writing, there is a caveat for setting Boolean properties in Winbatch. You might see VBA code in the format

Object.property = False (or True) and write corresponding Winbatch code as

Object.property = @FALSE (or @TRUE) and get an error, and then maybe try

Object.property = 0 (or 1), the values for True and False, and still get an error until you finally settle on

Object.property = ObjectType("BOOL",0) - which will work. Idiosyncrasies like this are common in all languages.

In the final section I will look more closely at moving VB/VBA code into Winbatch.

Another caveat concerns properties that start with "On�" ( the Excel.Application has an OnWindow property). These refer to events or procedures that are executed in response to a property state. Again, as of this writing, Winbatch cannot address events with OLE.

Another common term used in conjunction with properties is 'collections'. Often a collection is used to refer to an indeterminate number of objects within an object. For example, an instance of an Excel Application might have n-number of Worksheet objects associated with it. When we discuss MDAC, the properties associated with certain objects are considered collections. VBA has a For Each� Next coding construct,

For Each object in TheCollection
   ' display the name of the object
   ' display it's value
Next

Winbatch uses a nearly identical ForEach� Next as we shall shortly see.

Properties/collections and Winbatch UDF's:

If one were to be tasked with developing a suite of scripts which utilized the Excel Object it would be desirable to place repeated steps into a series of reusable code snippets. Winbatch provides both (U)ser-(D)efined (F)unctions and (U)ser-(D)efined (S)ubroutines to assist in this. Most WB scripts I have written for Excel begin:

oXL = 0
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Exit
oXL.Visible          = @TRUE  
oXL.ScreenUpdating   = @TRUE  
oXL.UserControl      = @FALSE
oXL.DisplayAlerts    = @FALSE

These 'defaults' more or less say "once my object is created, I want it to see visible on the screen; if I start manipulating data, I want the changes to appear; the object was created with CreateObject() [UserControl]; and finally if I save data, do not display any inherent Excel prompts [ARE You Sure?]". While these defaults might work 99.9% of the time, I might not want to re-write/re-compile a script if one of them needed changing. Since these properties are Boolean, I might want to set up an INI file

[XLDefaults]
visible = 1   
��

then have my script(s) begin with

#DefineFunction StartExcel()
oXL = 0
oXL = CreateObject("Excel.Application")
If oXL == 0 Then Return(0)
cINI = StrCat( dirScript(),"StartExcel.ini")
If ! FileExist(cINI) Then goto set
:get
oXL.Visible  = IniReadPvt("XLDefaults","visible",1,cINI)
oXL.ScreenUpdating  = IniReadPvt("XLDefaults","ScreenUpdating",1,cINI) 
oXL.UserControl = IniReadPvt("XLDefaults","UserControl",1,cINI)
oXL.DisplayAlerts   = IniReadvt("XLDefaults","DisplayAlerts",0,cINI)
Return(oXL)
:set
IniWritePvt("XLDefaults","visible",1,cINI)
IniWritePvt("XLDefaults","ScreenUpdating",1,cINI)
IniWritePvt("XLDefaults","UserControl",1,cINI)
IniWritePvt("XLDefaults","DisplayAlerts",0,cINI)
goto get
#EndFunction


oXL = StartExcel()
If oXL == 0 Then Exit

Unfortunately the numerous properties associated with the Excel Application Object are not considered a collection, therefore any kind of loop to determine their names or default values will fail. Other Object models (as will be illustrated in the section on MDAC) do expose their properties as collections which can be enumerated.

DB = CreateObject("ADODB.Connection")
ForEach p In DB.Properties
   Message(p.Name,p.Value)
Next
DB = 0
Exit

Once you start coding below Excel.Application, properties are more amenable to UDF's.

To illustrate: the majority of object manipulation in Excel is performed at the Worksheet level in the Object Model. Worksheets are a collection of objects derived from the Workbook object, derived in turn from the Application object. Once oXL (our Application Object variable) is set, something like either

oXL.WorkBooks.Add()
or oXL.WorkBooks.Open(xlsFileName)

can be followed by

oWK = oXL.ActiveWorkBook

oWK is a reference to a collection of Objects, and the following code will identify the name of each Worksheet in the collection:

ForEach w in oWK.WorkSheets
   Message( "WorkSheet Name",w.name)
Next

The fact that certain objects can be enumerated for their properties and values provides an opportunity for the Winbatch programmer to create reusable generic routines (UDF's or UDS's) for a particular Object Model. Refer to the Worksheet UDF in the source code for this section.

As you become more experienced with programming objects, you may discover situations where a ForEach construct will fail, while a For i=0 To (object.property.count)-1 will succeed (assuming the object properties are a 0=based array), or if that fails a For i=1 To (object.property.count) will succeed. This would neither be the fault of Winbatch or the creator of the object - but hopefully those discrepancies are few.

Methods:

A formal definition of an object method runs something like "A method is an operation or function that is associated with an object and is allowed to manipulate the object's data" - or perhaps more relevant to Winbatch - "A method is a function performed on a window or object through program coding." In either case, you invoke methods when you want to accomplish something. Let's take a quick look at some of the Excel Application Object methods.

MS Excel Object Model Sample 5

If oXL exists from having issued CreateObject("Excel.Application"), it is important to know that oXL.Quit() should be issued at the end of a script to physically close the Application. After this, oXL = 0 removes the object reference within Winbatch. In my experience, it has been a common mistake for a person to issue oXL = 0 before oXL.Quit() then post a question on the BBS as to why the 'object' was not truly closed. Remember, when an Excel (or any) Application object is opened, it has a life of its own and must be closed by it's own rules.

The Quit() method is an example that takes no parameters. By contrast, the Save() method

Not so Articulate Object Models:

As good as the Object Model is for Excel, you will eventually logon to a site where an author criticizes it for not permitting one thing or another to be easily done. Fortunately, Excel has a built-in macro capability which overcomes all but the staunchest criticism and this is covered in the last section. The central point is, if you are interested in writing Winbatch scripts for object manipulation Excel is probably your best starting point.

Since CreateObject() can be used with components other than a full-fledged application like Excel with a documented Object Model, you should keep in mind that these components usually are hierarchical and your script only needs to 'follow the dots' in the correct order (my way of oversimplifying hard work). For example, XML is all the rage. Microsoft has developed and freely distributes a suite of components known as MSXML with a huge Object Model. Many developers have found the Microsoft Model cumbersome and memory intensive. You would expect that someone would come up with 'kind of an' XML-lite product, and http://www.chilkatsoft.com/ChilkatXml.asp is one has a free ActiveX download. Since the product is free, the documentation is sparse (but you can purchase support). Rather than an Object Model, the help file merely list available methods and properties:

ChilkatXml Object Model Sample 1

and were you to select, say an individual method

ChilkatXml Object Model Sample 2

you receive just the bare essentials of what this method call would return.

Still, if you wished to use this component in a Winbatch script, with a little trial and error you can code this component (which is available for free, and can be distributed with scripts). Below is a snippet from a script to search for specific 'tags' in an XML file

oKat = CreateObject("ChilKatXml.ChilKatXml.1")
n = oKat.LoadXmlFile(cXML)
message("Children in Tree",oKat.NumChildren)
root = oKat.GetRoot()
oNode = oKat.SearchForTag(root, "ArticleTitle")

Another category of objects can be classified as 'limited accessibility'. The Adobe Acrobat model is a good example. The entire model is exposed only to the C language with a limited set of objects exposed to VB (and hence Winbatch). So rather than a strict hierarchy, the IAC ( Inter-Application Communication; Adobe supplies this as a PDF to explain the objects and methods exposed ) presents two 'layers' each as separate models -

The 'Viewer' Object
The 'Document Object

Refer to the script 'Adobe Acrobat Example' at the end of this section for an idea of how to manipulate Adobe objects.

If you plan to be serious about integrating OLE functionality into your scripts it would be in your best interest to obtain the Microsoft Platinum SDK which can either be downloaded or ordered on CD. It offers exhaustive documentation, examples and tools for OLE/COM programming.

MS Platform SDK page

Once you obtain this reference you can explore by topic or just type in a search criteria. Below, I typed in 'com'.

MS Platform SDK page 2

The SDK includes an Object/TypeLib viewer, which while written more for the C programmer, does allow you to examine the methods and properties for every recognized component on your PC.

OLE/COM Object Viewer

If you own Microsoft Office, Excel has a built in 'Object Viewer' which is easier to understand than the above viewer.

OLE/COM Object Viewer 2

Here, I searched for 'application'. I can drill down to the various members, discover decimal values which can be assigned to properties, or determine how I might invoke a particular method.

In Summary

The key to understanding an applications Object Model is to track down the documentaion for that Object Model. This can sometimes be tricky. Here is a link to a technical support article with some url links to various applications online Object Models: W14659

In many of the MS Office applications, if you hit ALT-F11 and you'll see the VBA Editor. You can then go up to the TOOLS menu and click REFERENCES. Go down the list until you find the section that starts with the name of the Object Model. For Example For Internet explorer you would select

Microsoft Internet Controls (shdocvw.dll) 
and
Microsoft HTML Object Library (MSHTML.TLB)

Click OK, and then hit F2. The object browser opens. Go up to the top where it says ALL LIBRARIES and find SHDOCVW and click it. You now have access to that Object Model. From here you can begin getting familiar with the object hieracrchy. Next thing you know you will be communicating directly with your application from your WinBatch script.

Article ID:   W16296

File Created: 2019:08:14:08:51:56

Last Updated: 2019:08:14:08:51:56

Database Search

TechHome