Office applications Toolkit

Make VB apps scriptable

Tags:
Engine,
Windows,
VB,
Scripting Demo

Lamont Adams Builder.com

Published: 26 May 2002 22:32 BST

Yes, that's right, the technology that was supposed to replace Network Administrator's reliance on BAT files with something a little more modern might have some usefulness for the "real" developer.

You could make a case that one reason for the popularity of Microsoft's Office suite is that all the applications featured in it include a simple development environment that allows technical users to automate repetitive tasks. Visual Basic for Applications (VBA) provides this environment in Office, and you can license VBA for use in your applications if you can afford the licensing fees that Microsoft would demand from you. In this article, I'll show you how the WSH and the Windows Script Control can provide a cheap but serviceable alternative to VBA for providing automation scripting in a Visual Basic application.

Getting your bearings
The Windows Script Control encapsulates the Active Scripting Engine, which is the heart of WSH, into a UI-less ActiveX control that you can easily use from VB. The control allows you to execute entire scripts or fragments of script written in any language supported by the WSH. You likely already have this control installed on your machine; it's named msscript.ocx and is usually located in your System32 folder. If you don't have it, it's available for download from Microsoft.

The control includes some documentation, but in my experience, it's somewhat incomplete and inaccurate. The best way to get your bearings is, unfortunately, to simply play around with it and see what works and what doesn't. Having done a little of that myself, I created a test project that illustrates some of what's possible with the scripting control, which you can download here. I'll be referring you to parts of that project throughout this article.

Running script
To run some script using the scripting control, you have several options:

The Eval method, which is similar to the VBA Eval function, allows you to evaluate expressions that range from simple arithmetic to complex expressions containing embedded script commands or object method calls, and returns the result to your application.
The ExecuteStatement method will execute any complete script statement.
You can use a combination of the AddCode and Run methods to load and execute custom script routines. This is the most powerful method, and is the one I'll cover in the remainder of this article.

Introducing ScriptingDemo
The ScriptingDemo app consists of a single form that displays a list of script files that it finds in the application's directory, a textbox for arguments to script procedures, and a single textbox that an executing script can use for output. When the application starts, it instantiates a custom collection object, colCustomers, which loads data from the Customers table of the SQL Server Northwind catalog to use as play data for our sample scripts. Figure A shows the application's main form at runtime.

Figure A

ScriptingDemo main form at runtime

When a script file is selected, the app reads in the code line-by-line and adds it to the scripting control's environment using the AddCode method. Clicking on the Run Script button causes the scripting control to attempt to execute a procedure with the same name as the selected file, sans extension.

Go to section: