page static_html.xsl b2003_4_9

Windows Scripting Host

In this article, I will both provide an initial XML sample application, but more importantly I will provide an overview of using the Windows Scripting Host (WSH) for desktop development. WSH is an incredible desktop development environment which is unfortunately overlooked by many people. Whether you are a developer or end user on the Windows platform, an investment in WSH will pay major dividends in terms of automating many tedious tasks. WSH gets installed with newer version of Internet Explorer. But to be certain, I would recommend downloading and installing the latest and greatest version - currently version 5.6 (see article 1 for URL).

You can think of WSH as being Macros (Word, Excel, etc.) which run directly on your computer instead of running within one of the Office suite of products. System administrators use WSH to perform maintenance tasks (managing accounts, cleaning up file systems, etc.) or to get reports on network resources ("which computers on our network have less than 5% of their disk capacity remaining?"). But my WSH examples will be mostly along the lines of a) open a report description, b) run each item in this report, and c) create a document (or page, slide, sheet, chart, etc.) out of the results.

You could legitimately ask the question "why not just use macros in Excel to automate Excel?" While I am not against this approach, I think it is worth understanding that when you run a macro, the host application is in charge. When you run a WSH program, you are in much greater control of the task and the results. Many of the approaches which I will demonstrate could be used within Office macros. But several things I do cannot easily be done as macros - or I have not found it worth the attempt. Also, Office macros cannot be written in JavaScript. I don't discourage you from migrating this work to macros, but I'll stick with WSH.

PowerPoint Automation

The program in pptauto.wsf demonstrates the basic style of WSH I will follow in this and in future articles. WSH scripts are actually XML files. The <runtime> tag contains a list of command line arguments to this program. For example,

<named
    name="ppt"
    helpstring="The PowerPoint file to use as a template."
    type="string"
    required="true"
/>

says that the parameter named "ppt" is a required string. WSH uses these declarations to determine if the user provided the necessary parameters and to provide a helpful message if they did not.

After the runtime section comes a list of <object> tags. These tags declare COM objects which will be globally available in the program. In this example I have declared a WScript.Shell and given it the ID "shell". The Shell object gives me access to the execution context of the script.

After the object declarations, there is a list of <script> tags which load external WSH libraries. In this program, I reference an external file named include.js. This file defines a function testinc(). This external function is merely here for demonstration purposes. It is a good coding practice to create reusable modules and to place them in files which can be included in other programs.

The final script tag has the code for this program, which in this case opens a PowerPoint file, edits a chart object in the single slide present in this file, and then saves the presentation. Comments in the code provide a step-by-step explanation. The program can be run by opening a DOS/Windows command window and typing

cscript pptauto.wsf /ppt:chart.ppt /out:chart-out.ppt

This will run the script, passing in chart.ppt as the value for the ppt parameter and chart-out.ppt as a value for the out parameter. This sample uses "Automation" to create the desired document. Automation involves calling the COM programmatic interfaces exposed by an Office product. Automation is one of two approaches I will use to generate Office-based reports from XML/A. The other approach is to directly create a file which can be opened by Office. PowerPoint does not have a published file format, so Automation is currently the only available method of generating a presentation. The next example demonstrates that Excel XP does support direct creating of .xsl files.

Excel XP XML Spreadsheet

Excel XP supports a file format called XML Speadsheet (XMLSS). XMLSS represents an early arrival of XML-enabled MS Office technology. This XML enablement has been greatly enhanced in Office 2003. The program in xmlss.wsf demonstrates that Excel documents can now be created without resorting to Automation. This WSH script loads a file named xmlss.xml, modifies the value of a cell, saves the file, and then opens the file in Excel XP. Before running the script, open ssxml.xml in Notepad. Unlike a regular .xsl file, this file is in XML Spreadsheet (XMLSS) format. XMLSS is a pure-text XML file format for Excel XP. So to create an Excel report, one option is to generate the appropriate text file and then load it into Excel. This is exactly what I will demonstrate in a future article. Note that we could have used the Automation approach as was done in the previous example. However, direct file generation has some important advantages - especially when the file is XML.

This script is run as follows. The /xml parameter specifies the inital XMLSS to load. After modifications are made by the script, it is saved to the file specified by the /xls parameter.

cscript xmlss.wsf /xml:xmlss.xml /xls:xmlss.xls

XML, for those of you not in the know, is the best thing in computing since the mouse. With XML, we can describe just about any complex system, operation, or result using a human readable format with only two markup constructs: elements and attributes. Microsoft and most other software technology companies are quickly retooling for XML-based information processing. And with good reason - XML is easily shared between systems and programs. It is this sharing of XML encoded information which makes declarative analytics possible.

The Office XP version of Excel had a fairly limited XML capability. Some things which cannot be expressed in XMLSS include macros, charts, and embedded objects. Perhaps new version (2003) has extended the XML API for Excel but not, I believe, the XMLSS capabilities.

Summary

So what does all this have to do with XML/A? Really nothing. I just wanted to make sure you were setup to run and understand WSH, XML, SOAP, etc. In the next article, I will apply these same scripting techniques to XML for Analysis.

downloads: b2003_4_9.zip (21K)