COM automation in KiXtart 2010

COM Automation is a way for applications (such as Word and Excel) to expose functionality to other applications, including scripting languages such as KiXtart. This provides an easy way to access properties and call methods of other applications from within a script.

Note: the new COM automation support in KiXtart 2010 replaces the OLE functions in previous versions of KiXtart.

Creating a Reference to a COM Object

A reference to a COM object can be created by assigning the return value of the CreateObject or GetObject function to a variable:

$Object = CreateObject("WScript.Shell")

$ExcelSheet = CreateObject("Excel.Sheet")

$Root = GetObject("LDAP://RootDse")

Releasing an Object

Object references are automatically released if and when a variable becomes out of scope. To explicitly release an object reference, simply assign the value 0 (zero) to the variable holding the object handle:

$Object = 0

Using an Object's Properties and Methods

You can use the object.property syntax to set and return an object's property values or the object.method syntax to use methods on the object. For example, you could set the Caption property of the Application object as follows:

$xlApp = CreateObject("Excel.Application")

$xlApp.Caption = "MyFirstObject"

You could call the Quit method of the Microsoft Excel Application object like this:

$xlApp.Quit

In general, it is a good idea to be as specific as possible when referring to methods or properties of objects defined by other applications or projects.

Default Properties

Some objects support a default property or method. KiXtart provides limited support for reading of default properties within string or numeric expressions. Default properties are not supported when assigning an object reference to a variable. KiXtart also does not support setting the value of default properties.

Sample 1: accessing a default property in an expression:

$XL = CreateObject("EXCEL.Application")

? SubStr($XL,1,10) ; will display ‘Microsoft’

Sample 2: assigning an object reference to a variable:

$XL = CreateObject("EXCEL.Application")

$AnotherReference = $XL ; Assigns reference to $XL

Note

Use of default properties is discouraged as it makes scripts harder to read and error-prone.

COM Automation Samples

The following three sample scripts demonstrate just a few of the ways in which COM automation can be used in KiXtart scripts. Please consult the Microsoft Developer Network (MSDN) for more information on the many possibilities of COM automation.

Sample 1: script using COM automation and Active Directory Services Interface (ADSI) to retrieve various global properties of an LDAP server:

$root = GetObject("LDAP://RootDSE")

$root.GetInfo

? "ADSPath: " + $root.ADSPath

? "GUID : " + $root.GUID

? "Name : " + $root.Name

? "Parent : " + $root.Parent

? "DNC : " + $root.defaultNamingContext

Sample 2: script using COM automation and Windows Management Instrumentation (WMI) to enumerate the logical disks of the local system:

$Drives = GetObject("winmgmts:").ExecQuery("select Name,DriveType from Win32_LogicalDisk")

if @error <> 0

? @error + " / " @serror

else

for each $Drive in $Drives

? $Drive.Name

next

endif

Sample 3: script demonstrating how to start Excel and add data to a worksheet:

$oXL = CreateObject("EXCEL.application")

if @error = 0

$oXL.Visible = 1 ; make Excel visible to the user

$Rc = $oXL.Workbooks.Add ; add a new workbook

$array = "Order #", "Amount", "Tax"

$oXL.Range("A1:C1").Value = $array ;add some columns

For $i = 0 To 19

$oXL.Cells(($i+2),1).Value = "ORD" + ($i + 1000)

$oXL.Cells(($i+2),2).Value = rnd() / 100

Next

;Fill the last column with a formula to compute the sales tax.

$oXL.Range("C2").Resize(20, 1).Formula = "=B2*0.07"

;Format the worksheet

$oXL.Range("A1:C1").Font.Bold = 1

$Rc = $oXL.Range("A1:C1").EntireColumn.AutoFit

$oXL.UserControl = 1

else

? @error + " / " @serror

endif