WINBATCH OLE PROGRAMMING

WINBATCH OLE PROGRAMMING - Part 2

by Stan Littlefield

Object Interfaces

Microsoft established COM (Component Object Model) as more or less a set of rules governing how different objects, processes or services communicate with each other. I will use Object Interfaces rather loosely to describe how a Winbatch script can take advantage of this communication. Example scripts and code snippets can be found in the source code section for Part 2. Part 3 will have more detail on code syntax, but the scripts in this section are excellent if you just want to dive in and get results.

Consider the Clipboard Object. Winbatch has core a function to place and retrieve Clipboard information (ClipPut and ClipGet). However, Winbatch cannot natively store an Excel Spreadsheet to the Clipboard and later paste it into a Word document. It can however interface with those objects to get the task done. The purpose of this section is not to re-hash COM, but to look at several task that involve Object Interfaces. In general, Winbatch code involving objects is very similar to VB/VBA code - which we will refer to as dotted code. Once an object is created, once or more references are connected to that Object via a "." [dot] - to accomplish a task. Objects can spawn sub-objects which can spawn sub-sub-objects and dotted code of their own. And because Winbatch is basically transferring power from it's core functions to another application, both strange and serious errors are possible. The sample scripts associated with this and other sections have been tested on Windows XP, Windows 2000 and Win98. They are instructional and do not write any information to the Registry and involve Microsoft Office or other well known and widely used products.

Excel and Powerpoint: This is something I have seen on several BBS sites - basically, how to automate the creation of Powerpoint slides from specific ranges, or worksheets in Excel. The task is broken down into (a) opening up an OLE communication with both Excel and Powerpoint; (b) selecting the range or worksheet in Excel as well as the means to copy it to the Clipboard; (c) activating Powerpoint, creating a new slide and pasting the Excel information. Of course, there are sub-steps and a required knowledge of 'things' (this would be the methods and properties discussed in Part 3) needed to accomplish the task without error. The example script walks through this basic process. In order to run it requires Excel and Powerpoint are installed, but all data is created within the script.

The script doesn't want to waste a lot of time, so it calls CreateObject() for both Excel and Powerpoint ASAP. Call it 'paranoid coding' but you start by creating an instance to Excel. If you typed "Excel.Aplication", or the object didn't exist, you would generate a Winbatch error "Invalid Class String" and the script would end. But if for some reason it was a legitimate object, but there was some other problem with the system, oXL would return a 0 you would exit accordingly. Then if the Excel object is opened, but there is a problem with Powerpoint, you will want to Quit Excel (because at this point it is not visible on your screen, and if .Quit() is not issued you have an orphan in memory.

oXL = CreateObject("Excel.Application")
If oXL == 0 Then Exit
PPApp = CreateObject("Powerpoint.Application.10")
If PPApp == 0
   oXL.Quit()
   oXL = 0
   Exit
EndIf
EndIf

Excel saved to PDF: I am again using Excel as an example, but the process would apply to a Word Document, an Access report or anything that can print to the Adobe PDFWriter or PDF Printer Driver. Though it is a more common practice to actually convert the document with Adobe Distiller and then save it, the print driver is quick and convenient for small documents. Again, an Excel worksheet is created from scratch. A UDF uses the Winbatch Printer Extender to list available printers (if a PDF Printer is in the list). Finally, the output can be accomplished with.

BoxText("Outputting to PDF")
currentPrinter = oXL.ActivePrinter
oXL.ActivePrinter = '%cPtr%'
cName  = oWS.Name
BoxText("Printing WorkSheet:%cName%")
oWS.PrintOut()
oXL.ActivePrinter = currentPrinter

However, the actual script use the Winbatch Printer Extender to handle selecting and re-setting the printer. It is worth noting that there are several shareware/freeware 'PDF-compatible' print drivers and this sample could be easily adapted to them.

Windows Management Instrumentation (WMI). Before Winbatch became serious with OLE, it was most useful for Network Administrators who required tools to deal with user accounts, installations, reviewing log files� etc. With WMI, Microsoft distributes a means to access information about services on a PC through an object interface. WMI is part of the Windows 2000/XP installation, and can be downloaded for Win98. The WMI interface exposes service classes, i.e Win32_BIOS to which you can execute SQL queries. I have included a simple WMI script which will gather basic information about a local PC. You can check for WMI with

#DefineFunction isInReg(cProg)
Return( RegExistKey(@REGCLASSES,cProg) )
#EndFunction

If ! isInReg("WbemScripting.SWbemLocator")
   Message("Error:Cannot Continue","WbemScripting.SWbemLocator not registered")
   Exit
EndIf

This script departs from CreateObject(), and instead instantiates WMI through GetObject()

oWMI = GetObject(StrCat("winmgmts:{impersonationLevel=impersonate}!\\",strComputer,"\root\cimv2"))

This saves a few steps, and you are likely to see GetObject() used in VB/VBA code you run across. To see how GetObject() is used with WMI, examine the script for using WMI to obtain ProgID information. Although the script appears to be after the same information obtained in Part 1 (through the Registry) there is extreme variance. In that script WMI is instantiated with

oWMI = CreateObject("WbemScripting.SWbemLocator")
oServ = oWMI.ConnectServer(PC)
oSecur = oServ.Security_
oSecur.ImpersonationLevel = 3

and queries are executed via the oServ Object, not the oWMI object. Going back to the differences in the information obtained. In the Registry query for ProgID's - one entry for Excel is entered as

Excel.Application.10,{00024500-0000-0000-C000-000000000046}

(note, only the ProgID and CLSID where written out). In the WMI query, Excel is found as

{00020813-0000-0000-C000-000000000046}0{90280409-6000-11D3-8CFE-0050048383C9},Microsoft Excel 10.0 Object Library,,Microsoft Excel 10.0 Object Library,,0,{00020813-0000-0000-C000-000000000046},Microsoft Excel 10.0 Object Library,,,,260

And although the CLSID's are equal, WMI references the Object Library for Excel (which is what you need if you are writing VB code). With WMI you can track down a specific TypeLib , DLL or OCX which contain methods and constants for the Object [Section 3]. Finally database. As I will be devoting an entire section to the Microsoft Data Access Components [MDAC], the last script in this section is meant to convey a simple technique to add a 'database' element to Winbatch which has no built in database support (Note: Winbatch does have a LAFFDB Extender, but that essentially writes out a .csv file). In a database, you are able to assign both a field name and field type for each element you wish to store and retrieve data for. During PC infancy, the term database was a file ( i.e. dBase or Paradox), but later with Client-Server and Access, database referred to a container within which n Tables were defined. And, when manipulating a Table as an Object, the term Recordset is used. So, let's use Recordset - MDAC comes with a persistence provider which is used to create an XML (file structure) of any Recordset. Further, MDAC permits declaring a Recordset, creating field names and field types, and entering data which can then be saved (or persisted) to the XML file and later treated like a regular database, i.e. it can be opened, indexes assigned, edited (viz. add/edit/delete or records), re-saved and even linked to a real table in a database. So, for example, the output from the script in Part 1 to gather ProgID's could be re-written to persist the information to a Recordset..

The Fabricated Recordset script shows how to create a 'database' file with several distinct field types, enter a row of data, persist it, and display the results. C and C++ programmers can validly argue that the script merely enumerates and stores a 'structure' - and that is fine since the point is not semantics but results.

Article ID:   W16295

File Created: 2014:07:18:09:27:30

Last Updated: 2014:07:18:09:27:30

Database Search

TechHome

WINBATCH OLE PROGRAMMING - Part 2

Object Interfaces