Application Automation

Overview

Specific operations are generally accessed in Windows Script Host (WSH) through a certain COM object for the related operation, such as CDO for e-mail operations or ADO for database functionality. Many of today's major desktop applications, such as Microsoft Office and Corel Office, expose rich object models that can be manipulated by WSH.

Program automation allows WSH to use features from applications to auto-mate operations, using the best features and capabilities of each application.

Many of these applications include features that allow for diverse data sources to be imported and manipulated, allowing WSH to automate building complex documents, which is not possible with the standard application import facilities.

Any Microsoft Office examples will run under Office 97 or Office 2000.

Note 

For more information, read the MSDN Library articles "Office Objects and Object Models" (http://msdn.microsoft.com/library/officedev/odeopg/deconunderstandingofficeobjectsobjectmodels.htm), "Microsoft Office 2000 Developer Object Model Guide" (http://msdn.microsoft.com/library/officedev/odeomg/deovrobjectmodelguide.htm), and "Working with Office Applications" (http://msdn.microsoft.com/library/officedev/odeopg/deovrworkingwithofficeapplications.htm).

Creating Formatted Word Documents

Problem

You want to create a formatted Word document.

Solution

The following script builds a document by automating Word:

On Error Resume Next
 'attempt to get an existing running copy of Word
 Set objWord = GetObject(, "Word.Application")
 'if error occurred, then couldn't find Word, create new instance
 If Err Then
 Err.Clear
 Set objWord = CreateObject("Word.Application")
 End If
 objWord.Documents.Add
 objWord.Selection.TypeText "Hello World!"

Discussion

You can build Office documents by automating Office applications.

Before you even attempt to manipulate an application, try and identify features in the application that you want to use that can simplify the creation of the document. For both Word and Excel, these features include the following:

• Templates: Predefined, reusable document layouts
• Styles: Predefined character formatting
• Bookmarks (Word) or range names (Excel)

A template is simply a document that contains a predefined layout. To create a template, create a new document by selecting File > New, but instead of creating a document, select the Template option, as shown in Figure 9-1.

Figure 9-1: New document dialog box

Build the general layout of the document, but don't populate any areas with information.

Use bookmarks to identify areas in the document you want to quickly and easily navigate to, such as a data entry field. This is a better alternative than programming navigation keystrokes, because if any changes to the document are made, recorded keystrokes may move to the wrong location.

Document styles allow predefined formatting to be applied to a paragraph or selected text area. This might be a combination of various formatting elements, such as fonts and visual formatting (e.g., bold, italic, and so on). Excel supports styles, but it is not as obvious as Word (you must add the Style button to the Excel toolbar).

Both Excel and Word support creating styles by example. To create a style by example, format a paragraph with the formatting attributes you want to apply (fonts, colors, and borders). Then click in the Style box on the toolbar, as shown in Figure 9-2, and type in the name of the style.

Figure 9-2: Style drop-down box

Using the application features not only minimizes programming code and saves time, but it can also make maintaining the solution easier. Instead of changing the code to update formatting, you can make changes to the appropriate style or document template. If you use bookmarks to navigate to certain areas of a document, you can add new text to the template without affecting the existing document navigation.

To control an application, you must create or get a reference to the application object. References to objects are retrieved by using the object type name. The object type name varies from application to application. In the case of Office applications, it's the application name followed by a period and the term "application," so the Word ProgID name is Word.Application.

You can make references to Office applications that are already running by using the GetObject method:

Set objApplication = GetObject([strFile][,strApp])

strFile allows the creation of a document object based on the application associated with the file. If you were to specify a valid filename ending with a .doc extension, a reference to a Word document object would most likely be returned (assuming Word is installed and associated with the .doc extension):

'get a reference to the Word file report.doc
Set objDocument = GetObject("d:data
eport.doc")

Specifying the strApp parameter together with strFile will attempt to open the file with the specified application ProgID. This is useful if more than one application is associated with the file extension.

'get a reference to the Word file report.doc
Set objDocument = GetObject("d:data
eport.doc", "Word.Application")

If strApp is specified on its own, a reference to an existing running copy of the application is returned. If no copy is found, an error is generated. You can use this code to use an existing copy of an application instead of creating a new instance:

On Error Resume Next
 'attempt to get an existing running copy of Word
 Set objWord = GetObject(, "Word.Application")
 'if error occurred, then couldn't find Word, create new instance
 If Err Then
 Err.Clear
 Set objWord = CreateObject("Word.Application")
End If

Now that you got a reference to the application, you can start constructing the document. When it comes to the actual Office application code, don't try to manually build the code. Word, Excel, and the latest version of PowerPoint support recording macros. These macros are VBA code, which can be converted to work in a WSH script with a few minor changes. Create the macro and perform a "dry run," simulating the steps required to create the document, as well as performing any operations that will be used in your script, such as creating, saving, and closing the document. The following Word macro jumps to the bookmark called TableStart, fills in a few table cells of data, and selects and merges a table cell:

Selection.GoTo What:=wdGoToBookmark, Name:=" TableStart"
Selection.TypeText Text:=" name"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=" type"
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=" price"
Selection.MoveRight Unit:=wdCell
Selection.SelectRow
Selection.Cells.Merge

Using the macro recorder, you can easily build the logic for 90 percent of the solution and simply cut and paste the results into your script. VBA code generated by the macro recorder requires some changes.

VBScript doesn't support the method of parameter passing used by VBA, where the parameter name is passed with an assignment operator followed by the value. The following VBA statement:

Selection.MoveRight Unit:=wdCell

would be changed to this:

Selection.MoveRight wdCell

Another issue that you will encounter is constants. Macros include the application-specific constants. You can manually add these constants to your scripts or you can use the element provided by WSF script files to add them:

You would assume that the Word.Application application ProgID would be used to get a reference to the type library for Word, but this doesn't work. This is because the type library for Word is not associated with Word.Application. It is, however, associated with Word.Document, so using this ProgID instead will provide access to all constants exposed through the application.

You may be required to specify version information when using the element with certain versions of Office. For Office 2000, the version number is 8.0:

The recorded script methods are part of the Application object. Use the With statement together with the Application object followed by the application methods:

All Office applications (except Access) expose the active documents through a collection. These collections are named Documents for Word, Worksheets for Excel, and Presentations for PowerPoint.

'list active Word documents
Set objWord = GetObject(, "Word.Application")
 'loop through each Word document
 For Each objDoc In objWord.Documents
 Wscript.Echo objDoc.Path, objDoc.Name
 Next

The Documents collection allows for any active documents to be referenced by either document name or number. The document numbers start at the offset 1.

'get a reference to an existing copy of Word
 Set objWord = GetObject(, "Word.Application")
'get a reference to the first document
 Set objDocument = objWord.Documents(1)
'get a reference to the document Data.doc
 Set objDocument = objWord.Documents("Data.doc")

The following example gets a reference to an existing running copy of Word and closes any open documents:

'quitword.vbs
'Finds a running copy of Word, saves and closes all files
Dim objWord, objDoc
'get an instance to an existing copy of Word
 Set objWord = GetObject(, "Word.Application")
 For Each objDoc In objWord.Documents
 'check if the filename is empty
 If objDoc.Path = "" Then
 objDoc.SaveAs objDoc.Name
 objDoc.Close
 Else
 objDoc.Close True
 End If
 Next

objWord.Quit

While recording macros provides an easy way to build script logic, you may want to fine-tune your application by manipulating the generated code.

One way of investigating what commands and operations are available to you (other than Help references) is to use an object browser. All recent Microsoft Office application releases (Word, Excel, Access, and PowerPoint) provide access to the object browser through the VBA development environment.

Use the Office object browser to view the properties and methods the applications expose. The object browser is available by starting the Visual Basic Editor from the Tools > Macro menu in Word, Excel, or PowerPoint. In Access, create a new or edit an existing code module.

Once you are in the Visual Basic environment, press F2 to display the object browser, as shown in Figure 9-3.

Figure 9-3: Object browser

You can use the object browser to view any method or property. The browser will show the syntax for any given method as well as parameter and return value data types.

By default, you are able to view the object model for the application you are currently in. You may want to view the object model for another application or automation object, such as the ADO, CDO, or FSO object models.

To add object references, select Tools > References. The dialog box shown in Figure 9-4 appears.

Figure 9-4: References dialog box

The References dialog box lists all available object references. To select or deselect a given reference, click the check box beside the object you want to browse.

If you do not have an Office or Visual Basic application installed, an alternative is to use the freely available automation object browser from Microsoft, the OLE/COM Object Viewer. It is available for download from http://www.microsoft.com/Com/resources/oleview.asp, and it is also included in NT Resource Kits and MSDN subscriptions.

The OLE/COM Object Viewer is a less friendly version of the object browser included with the VB/VBA environment, but it does allow viewing of object properties, methods, and constants, as well as easy retrieval of an object's class ID (CLSID), which is useful for referencing objects when a ProgID is unavailable or unknown. Figure 9-5 shows the CLSID for the Word object library.

Figure 9-5: Object viewer

The following text file, cars.txt, contains data to be used in building a formatted Word document:

"BMW"," Z3"," Sports",50000
"GM"," Grand Am"," Sedan",25000
"GM"," Yukon"," Truck",40000
"Ford"," Mondeo"," Sedan",23000
"Ford"," Festiva"," Compact",12000
"Ford"," Explorer"," Truck",35000
"Hyundai"," Excel"," Compact",17000

The cars.txt file is used by the following script to build a Word document:

The script opens the cars.txt file and gets a reference to an instance of Word. A new document is created using the template carlist.dot. The script uses the ExtractCSV function from Solution 8.2 to extract CSV values from a string into an array.

Note 

For more information, read the articles "Using the Object Browser" (http://msdn.microsoft.com/library/officedev/odeopg/deconusingobjectbrowser.htm), "Microsoft Word Object Model" (http://msdn.microsoft.com/library/officedev/odeomg/deovrmicrosoftword2000.htm),"Working with Microsoft Word Objects" (http://msdn.microsoft.com/library/officedev/odeopg/deovrworkingwithmicrosoftwordobjects.htm), "Using the OLE/COM Object Viewer" (http://msdn.microsoft.com/library/en-us/vccore98/HTML/vcrefusingolecomobjectviewertoviewcontrolsinterfaces.asp), and "OLE/COM Object Viewer" (http://www.microsoft.com/Com/resources/oleview.asp).

See Also

Solution 8.2.

Identifying Office Documents by Their Properties

Problem

You want to identify all Office documents that have certain properties.

Solution

To list the properties stored in a document, iterate through the BuiltinDocumentProperties collection and display the value for each property object:

'get a reference to a document
Set objDoc = GetObject("d:dataword
eport.doc")

On Error Resume Next
For Each objProp In objDoc.BuiltinDocumentProperties
 Wscript.Echo objProp.Name, objProp.Value
Next

Discussion

All Office documents allow you to set document properties. These properties can be used to store document information-for example, author, keyword description, and creation information. To set these properties, choose File > Properties. The Document Properties dialog box appears, as shown in Figure 9-6.

Figure 9-6: Document Properties dialog box

Information is exposed through the BuiltinDocumentProperties property of a Word Document, Excel Worksheet, or PowerPoint Presentation object. This property is a collection that stores each value set in the file's properties page. Each property in the collection is stored as a property object, with a Name, Value, and Type property.

Most single-word property names that appear in the file property dialog box have the equivalent property name in the BuiltinDocumentProperties collection. These include Title, Subject, Author, and Keywords. To reference a particular document property, use the following code:

'get a reference to a document
Set objDoc = GetObject("d:dataword
eport.doc")
Wscript.Echo "The author is:" & objDoc.BuiltinDocumentProperties("Author")

Table 9-1 lists additional properties with longer names. The data type is string unless otherwise noted.

Table 9-1: Document Properties

PROPERTY	DESCRIPTION
Revision number	Document revision number.
Last print date	Date last printed. Date type.
Creation date	Document creation date. Date type.
Last save time	Date document last saved. Date type.

The following command-line script, offprops.vbs, allows for queries to be executed against all Office documents in a specified path:

'offprops.vbs
'lists office documents that contain document properties that
'meet certain criteria
Dim strCriteria, bSubDirs
Dim objRegExp, objEvent
bSubDirs = False
'check that two arguments are being passed
If Wscript.Arguments.Count <> 2 Then
 ShowDetails
 Wscript.Quit
End If
 'create a FSO and recursedir object (see Chatper 5)
 Set objEvent = Wscript.CreateObject ("WSH.RecurseDir"," ev_")
 'set the path to search and get criteria
 objEvent.Path = Wscript.Arguments(0)
 strCriteria = Wscript.Arguments(1)
 'filter only on DOC, XLS and PPT documents
 objEvent.Filter = "^w+.(doc|xls|ppt)$"
 'replace ` (ASCII 96) characters with double quotes
 strCriteria = Replace(strCriteria, "`", chr(34),1,-1,1)
 'replace all instances of document criteria doc.property with
 'objDoc.BuiltinDocumentProperties(property)
 Set objRegExp = New RegExp
 objRegExp.Pattern = "[w+]"
 objRegExp.IgnoreCase = True
 objRegExp.Global = True
 strCriteria = objRegExp.Replace(strCriteria, GetRef("Repl"))
 strCriteria = Replace(strCriteria, "_", "",1,-1,1)
 Call objEvent.Process()
Sub ShowDetails
 WScript.Echo "offprops Queries office document properties." & vbCrLf & _
 "Syntax:" & vbCrLf & _
 "offprops.vbs path criteria" & vbCrLf & _
 "path path to search" & vbCrLf & _
 "criteria office property criteria "& vbCrLf & _
 "Example: List all documents authored by Fred Smith "& vbCrLf & -
 " offprops.vbs d:dataword ""[Author]= `Fred Smith`"""
End Sub
'

Function Repl(strMatch, nPos, strSource)
 Repl = "objDoc.BuiltinDocumentProperties" & _
 "(""" & Mid(strMatch,2, len(strMatch)-2) & """)"
End Function

Sub ev_FoundFile(strPath)
 Dim objDoc, bResult
 On Error Resume Next
 'get reference to document found
 Set objDoc = GetObject(strPath)
 bResult = Eval(strCriteria)
 If bResult And Not Err Then
 If Not Err Then
 Wscript.StdOut.WriteLine strPath
 Else
 Wscript.StdErr.WriteLine "Error opening file "& strPath _
 & vbCrLf & "Error:" & Err.Description
 End If
 End If
End Sub

The command-line syntax for offprops.vbs is as follows:

offprops.vbs DocPath Query

DocPath represents the directory path to start the search. The script recursively searches the directory and all subdirectories for files that meet the criteria. Any files that meet the criteria are piped to the standard output.

The second parameter, Query, contains the query to execute against each file. It can contain any valid VBScript function and logical Boolean operators, such as AND, OR, and NOT.

Document property names must be surrounded by square brackets, so the Creation date property would be represented as [Creation date].

Any double quotes used in the criteria must be represented by the grave accent (`) character (ASCII value 96). This character is replaced with double quotes by the script.

The following examples demonstrate the script. To list all Word documents created by Fred Smith:

offprops.vbs "d:dataword" "[Author]= `Fred Smith`"

To list all files that contain the keyword "Finance":

offprops.vbs "d:dataword" "Instr([keywords], `Finance`)>0"

The script uses the WSH.RecurseDir scripting component from Chapter 5 to recursively search the subdirectories.

Note 

For more information, read the MSDN Library article "BuiltInDocumentProperties Property" (http://msdn.microsoft.com/library/en-us/office97/html/output/F1/D4/S5A92C.asp).

See Also

Solution 5.10.

Importing Data into Excel

Problem

You want to import data into Excel.

Solution

The following script, xlimport.wsf, imports comma-delimited input from standard input in an Excel spreadsheet:

Discussion

Much of an Excel script solution can be implemented by recording an Excel macro and inserting the results into a script, much the same as demonstrated with Word in Solution 9.1.

More complex manipulation of a spreadsheet requires mastering the Range object. All navigation, selection, and manipulation of a spreadsheet are performed using the Range object.

A range in a spreadsheet represents an area of one or more cells. Use Excel named ranges to identify specific areas of the spreadsheet. Named ranges can be defined by selecting a range of cells in a spreadsheet and entering a valid range name in the Range Name box.

Creating named ranges is useful for defining an area that you might need to navigate to. The advantage of using named ranges over fixed cell references (e.g., A10) is if areas of the spreadsheet are moved, appended, or deleted in the range's vicinity, the range reference updates accordingly.

The following example inventories the specified computer using WMI:

'create an instance of the ENTWSH.SysInfo object. This object
'is created In Solution 10.1
Set objSysInfo = CreateObject("ENTWSH.SysInfo ")
'attempt to get reference to running copy of Excel
Set objExcel = GetObject(," Excel.Application")
'if no running copies of Excel, start a new one
If Err Then Set objExcel = CreateObject("Excel.Application")
With objExcel
 'create a Excel workbook based on the inventory template
 .Workbooks.Add "D:Program FilesMicrosoft OfficeTemplatesinventory.xlt"
.Application.Goto "BIOS"
 .ActiveCell.FormulaR1C1 = objSysInfo.BIOSVersion
.Application.Goto "ComputerName"
 .ActiveCell.FormulaR1C1 = "computername"
 .Application.Goto "OSSerial"
 .ActiveCell.FormulaR1C1 = objSysInfo.SerialNumber
.Application.Goto "VirtualMemory"
 .ActiveCell.FormulaR1C1 = objSysInfo.VirtualMemory
.Application.Goto "CPU"
 .ActiveCell.FormulaR1C1 = objSysInfo.CPU
.Application.Goto "Memory"
 .ActiveCell.FormulaR1C1 = objSysInfo.Memory
.Application.Goto "OSVersion"
 .ActiveCell.FormulaR1C1 = objSysInfo.OS
.Application.Goto "OSUser"
 .ActiveCell.FormulaR1C1 = objSysInfo.RegisteredUser
End With

The previous sample uses an Excel template called inventory.xlt to build a new document. Cells used to store system information are defined by range names. The Goto method is used to jump to the cells in order to populate them with the appropriate information. The script uses the ENTWSH.SysInfo Windows Script Component from Solution 10.1.

To create a range, call the Name collection's Add method:

Set objExcel = CreateObject("Excel.Application")
With objExcel
 .Visible = True
 'create a new Excel workbook
 .Workbooks.Add
 'create a range called SourceData that references the range B2 to F10
 .ActiveWorkbook.Names.Add "SourceData", "=$B$2:$F$10"
End With

In the preceding example, the spreadsheet area that is specified for the named range is displayed in absolute format, in which each row/column that is referenced is prefixed by a dollar sign ($). If the range was specified in relative format (i.e., no dollar signs), the range name would be added relative to the active cell.

Using range names makes it easy to identify the location, size, and amount of information stored in the range.

A range is composed of one or more cells. You can reference individual cells by using an absolute spreadsheet reference (letter and column combination-for example, A1) or a named range:

'display value from cell A1 from the current active worksheet
Wscript.Echo objExcel.Range("A1")

'display value from named range UserName from the active workbook
Wscript.Echo objExcel.Range("UserName")

'display value from named range UserName from the first workbook
Wscript.Echo objExcel.WorkSheet(1).("UserName")

'display value from named range Computer Name from the first worksheet from
'the inventory2 workbook
Wscript.Echo objExcel.Workbooks("inventory2").Worksheets (1).range("ComputerName")

You can also apply formatting such as fonts, colors, and shading to a Range object:

'xlformat.vbs
Const xlSolid = 1
Const Red = 3
Const Yellow = 6
'create new instance of Excel application
Set objExcel = CreateObject("Excel.Application")
With objExcel
 .Visible = True
 'create a new Excel workbook
 .Workbooks.Add
 'set the font color for range A4:F8 to red
 .Range("A4:F8").Font.ColorIndex = Red
 'set the fill of current selected cells to solid yellow
 With .Selection.Interior
 .ColorIndex = Yellow
 .Pattern = xlSolid End With
End With

Formatting is applied to all cells in the specified range. The Borders, Font, Interior, and Style properties allow for formatting to be set on a Range object.

While you can use online Help to find out property values for formatting, the easiest way to determine what to set is to record a macro and apply the formatting you want to use. The resulting recorded macro will contain all the appropriate constants.

Individual cells within a range are represented as Range objects. A Range object is a collection of these individual cells and it can be enumerated. This allows for the processing of each cell in the range:

'xlupd.vbs
'updates values in specific range based on criteria
'create new instance of Excel application
Set objExcel = CreateObject("Excel.Application")

With objExcel
 .Visible = True
 'load an existing spreadsheet
 .Workbooks.Open "C:data.xls"
 Set objRange = .Range("Prices")
 'go through each cell in the range
 For Each objCell In objRange
 'update cell value according to current value
 If objCell.Value<100 Then
 objCell.Value = objCell.Value * 1.04
 ElseIf objCell.Value< 200 Then
 objCell.Value = objCell.Value * 1.05
 Else
 objCell.Value = objCell.Value * 1.07
 End If
 Next
End With

Referencing cells using spreadsheet row/column (e.g., A5) can be tedious if you want to access a cell's content using a numeric column reference. The Cells property returns a reference to a range specified by a numeric row/column combination:

Set objExcel = CreateObject("Excel.Application")
With objExcel
 .Visible = True
 'create a new Excel workbook
 .Workbooks.Add
 For nCol = 1 to 20
 For nRow = 1 to 20
 .Cells(nRow, nCol) = (nRow - 1) * 20 + nCol
 Next
 Next
End With

Both row and column offsets start at 1, so Cells(2,3) returns a reference to the contents of cell C3.

Excel doesn't provide any simple navigation methods that provide movement in any direction of a spreadsheet (such as move left, right, up, or down). The Range object's Offset property returns a range offset by a specified number of rows and columns from a range.

Use the Offset property together with the Select method to navigate a spreadsheet:

Set objExcel = CreateObject("Excel.Application")
With objExcel
 .Visible = True
 'create a new Excel workbook
 .Workbooks.Add
 For nCol = 1 to 20
 For nRow = 1 to 20
 'insert a value into the current cell
 .Selection = (nRow - 1) * 20 + nCol
 'move one column to the right
 .Selection.Offset(0,1).Select
 Next
 'move one row down and 20 cells to the left
 .Selection.Offset(1,-20).Select
 Next
End With

Specifying a negative offset moves the selection by either a negative number of rows or a column offset.

Note the class GUID was used to reference the Excel type library:

Using the Excel.Application reference to access the type library wouldn't work. Word uses a reference to the Word.Document class ID (CLSID), which returns a reference to the required type library.

Excel can't make a reference to a type library in a similar way using Excel.Worksheet, so the object's CLSID must be passed instead. Application CLSIDs can be referenced using Microsoft's class OLE/COM Object Viewer, as shown in Figure 9-7.

Figure 9-7: OLE/COM Object Viewer

The Solution script, xlimport.wsf, allows for comma-delimited standard input to be imported into an existing spreadsheet. The script appends the data to a named range specified in the command line.

If the specified range name doesn't exist, it is created. The new range is created starting in the first column, below the last row of existing data. The syntax for the script is as follows:

xlimport.wsf FilePath RangeName

FilePath specifies the path to Excel file to update. RangeName is the name of the range to update or create. The following command-line sample redirects the text file dat.txt into the xlimport script, which will append the contents to the range dat2:

cscript xlimport.wsf d:ook1.xls dat2 < dat.txt

Note 

For more information, read the MSDN Library articles "Working with Microsoft Excel Objects" (http://msdn.microsoft.com/library/officedev/odeopg/deovrworkingwithmicrosoftexcelobjects.htm) and "Microsoft Excel Object Model" (http://msdn.microsoft.com/library/officedev/odeomg/deovrmicrosoftexcel2000.htm).

See Also

Solution 10.2.

Generating Thumbnail Images for Web Pages

Problem

You want to automate the generation of thumbnail images for Web page design.

Solution

Corel PHOTO-PAINT exposes a COM automation interface that can be accessed from any environment that can manipulate COM objects. The following command-line script, thumbnail.vbs, converts images from a specified directory into thumbnails using CorelDRAW version 8:

'thumbnail.vbs
Const Height= 18
Const JPEG = 774
Dim aMenus, nF, strPath, objCorel
Dim objFSO , objFolder, objFile, strNew
Dim strDestination, strSource
If WScript.Arguments.Count <> 2 Then
 ShowUsage
 WScript.Quit
End If

 'get destination path and menu names
 strSource = Wscript.Arguments(0)
 strDestination = Trim(Wscript.Arguments(1))
'make sure destination path ends in a backslash
 If Not Right(strDestination,1) = "" Then _
 strDestination = strDestination & ""
Set objCorel = CreateObject("CorelPhotoPaint.Automation.8")
'get a reference to the source folder to read
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 Set objFolder = objFSO.GetFolder(strSource)
 For Each objFile In objFolder.files
 strNew = objFile.Name
 strNew = Left(strNew, InStr(strNew, ".") - 1) & "tm.jpg"
 CreateThumbnail objFile.Path, strDestination & strNew
 Next

Sub ShowUsage()
WScript.Echo _
 "thumbnail.vbs creates jpg. image thumbnails ." _
 & vbCrLf & "Syntax:" & vbCrLf & _
 "thumbnail.vbs Source Destination" & vbCrLf & _
 "Source path to source directory with images" & vbCrLf & _
 "Destination destination directory to store thumbnails" & vbCrLf & _
 "Example:" & vbCrLf & "thumbnail.vbs d:pictures d:pictures	humbs"
End Sub

Sub CreateThumbnail(strSource, strDestination)

 With objCorel
 'arguments 2 to 5 represent left, top, right, bottom coordinates '
 'of image. Argument 6 represents load type, 7 and 8 are used
 'if movie file is being loaded and represents start and end frame.
 .FileOpen strSource, 0, 0, 0, 0, 0, 1, 1
 'check if width is greater than height and resize accordingly
 If objCorel.GetDocumentWidth < objCorel.GetDocumentHeight Then
 'arguments 1 and 2 repesent width and height. 3 and 4 are
 'horizontal and vertical resolution in dots per inch and
 'argument 5 is anti-aliasing flag, which if True sets
 'anti-aliasing on
 .ImageResample 107, 143, 144, 144, True
 Else
 .ImageResample 144, 108, 144, 144, True
 End If
 'save resized. Second argument represents image format and third
 'is compression format used
 .FileSave strDestination, JPEG, 0
 .FileClose
 End With
End Sub

Discussion

Creating thumbnails for browsing images on Web pages is an effective way of navigating pictures. Creating thumbnails can be a time-consuming exercise because it involves manually resizing images to the appropriate size.

Corel PHOTO-PAINT implements a scripting language that can be used to automate tasks. The scripting language accesses PHOTO-PAINT operations through a COM interface, which can also be manipulated through WSH.

Note 

The following steps are implemented using Corel PHOTO-PAINT version 8. Older versions may not support these operations, and the steps may vary in more recent implementations.

PHOTO-PAINT includes a script recorder that you can use to record the steps you want to automate, as you would use the Word or Excel macro recorder.

To record a script, select the Record Script icon from the Recorder roll-up. Figure 9-8 shows the Recorder roll-up after a few operations have been recorded.

Figure 9-8: Recorder roll-up

Perform the operations you want to automate. Each recorded step will appear in the Recorder window.

When script recording is finished, save the script to a file. The script format is saved as a text ASCII file. Open the script file with a text editor or the Corel script editor:

WITHOBJECT "CorelPhotoPaint.Automation.8"
 .SetDocumentInfo 640, 480
 .ImageResample 145, 109, 144, 144, TRUE
 .FileSave "C:PhotoWise ImagesJun10Image02sm.jpg", 774, 0
END WITHOBJECT

The recorded script can be easily converted to WSH by replacing the With statement and adding a line to create the PHOTO-PAINT object:

Set objPaint = CreateObject("CorelPhotoPaint.Automation.8")
With objPaint
 .SetDocumentInfo 640, 480
 .ImageResample 145, 109, 144, 144, TRUE
 .FileSave "C:PhotoWise ImagesJun10Image02sm.jpg", 774, 0
End With

The Solution script, thumbnail.vbs, is a command-line script that creates a JPEG thumbnail file from all graphics files found in the directory specified in the command line:

thumbnail.vbs Source Destination

The Source parameter is the name of the directory to find images in. The Destination parameter points to the directory path to store the thumbnails. The source and destination directories must be different. The following command line creates thumbnails of all images in d:datapictures and saves them to d:datapictures humbnails:

thumbnail.vbs "d:datapictures" "d:datapictures	humbnails"

The image is resized to a thumbnail and saved to the destination directory specified by the destination parameter. The filename is padded with tn to identify it as a thumbnail.

See Also

Search the Corel Script reference (scedit.hlp) installed with Corel PHOTO-PAINT.

Building Web Page Rollover Images

Problem

You want to create rollover images for Web page menus.

Solution

The following script creates rollover images by automating Corel PHOTO-PAINT:

'buildmenus.vbs
Const Height = 18
Dim aMenus, nF, strPath, objCorel

 If Wscript.Arguments.Count <> 2 Then
 ShowUsage
 Wscript.Quit
 End If

 'get destination path and menu names
 strPath = Wscript.Arguments(0)
 aMenus = Split(Wscript.Arguments(1),";")

 Set objCorel = CreateObject("CorelPhotoPaint.Automation.8")

 'loop through and build menu elements
 For nF = 0 To UBound(aMenus)
 'build
 BuildElements CStr(aMenus(nF)), strPath _
 & aMenus(nF) & "ON.jpg", 0, 0, 0
 BuildElements CStr(aMenus(nF)), strPath _
 & aMenus(nF) & ".jpg", 255, 255, 255
 Next

Sub ShowUsage()
WScript.Echo _
 "buildmenus.vbs builds on/off images for Web rollovers ." _
 & vbCrLf & "Syntax:" & vbCrLf & _
 "buildmenus.vbs Path Menus" & vbCrLf & _
 "Path path where images are stored" & vbCrLf & _
 "Destination Titles for each button, separated by semicolon" & vbCrLf & _
 "Example:" & vbCrLf & "buildmenus.vbs d:images Home;Shop;Help"
End Sub
Sub BuildElements(strText, strFileName, nRed, nGreen, nBlue)
 Dim nWidth, nHeight
 'calculate width of box
 nWidth = Int(7 * Len(strText))
With objCorel
 'create a new file with white background
 .FileNew nWidth + 2, HEIGHT, 1, 72, 72, False, _
 False, 1, 0, 0, 0, 0, 255, 255, 255, 0, False
 'draw a blue rectangle
 .RectangleTool 0, 0, 0, 0, True, False, True
 .FillSolid 5, 32, 102, 176, 0
 .Rectangle 0, 0, nWidth, HEIGHT
 'add centred text box
 .TextTool nWidth / 2, 2, strText
 .SetPaintColor 5, nRed, nGreen, nBlue, 0
 .TextSettings 400, False, False, 1, "Arial", 14, True, 0, 100, 0, False
 'save file and close
 .FileSave strFileName, 774, 0
 .FileClose
 End With
End Sub

Discussion

All image and text manipulation facilities implemented in PHOTO-PAINT can be scripted.

While creating the script to create these images can initially be time-consuming and tedious, once the logic is in place a lot of effort can be saved producing images that contain a set format.

Web page "rollover" menus are one such example. Rollovers are the effect where the mouse moves over an image the image changes to highlight the action. This requires at least two images for each menu.

The buildmenus script creates two sets of images based on parameters passed through the command line. The first image is a white-on-blue menu button, while the second is the highlighted button with a black-on-blue text button:

buildmenus DestinationPath Menus

The DestinationPath parameter identifies the location where the images will be stored. It can be a local or UNC path and must end with a backslash ().

The Menus parameter contains the name of the menus separated by semicolons.

The following command line generates eight images and stores them in the d:wwwrootimages directory:

buildmenus "d:wwwrootimages" "Products;Information;Support;Purchase"

For each menu there would be a standard button saved as a JPEG file using the name of the menu, such as products.jpg, information.jpg, and so on, as well as a corresponding image for the highlighted menu.

Highlighted images take the name of the menu with "ON" appended to it- for example, ProductsON.jpg, InformationON.jpg, and so on.

See Also

Search the Corel Script reference (scedit.hlp) included with the CorelDRAW installation.

Generating Electronic Copies of Access Reports

Problem

You want to generate electronic copies of Access reports.

Solution

Microsoft Access can generate electronic "snapshots" of reports. The following script generates a snapshot using the Northwind sample database:

Discussion

Access 97 with Service Release 2 (SR2) and Access 2000 allow you to generate report "snapshots." A snapshot is an electronic copy of the report that you can view in a separate viewer application.

You do not need the Access application installed on your computer to view snapshots; you just need the Snapshot Viewer, which is available as a download from the Microsoft Web site (http://www.microsoft.com/).

To output an Access report to an external file, use the DoCmd.OutputTo procedure to build the file using a specified output format. Reports can be output to HTML, Active Server Pages (ASP), plain text, and Excel, as well as the Snapshot format.

For a simple report that doesn't require any parameters, specify the report name, type, and destination:

Reports that require parameter values entered in a form are a bit trickier because the parameter cannot be passed to the report. Open the form using the DoCmd.OpenForm method and get a reference to the appropriate form through the Forms collection.

Set the parameters required by referencing the required fields through the form's Controls collection.

This Solution uses the Northwind sample database that is supplied with Access.

Note 

For more information, read the articles "Using Access 97/2000 Report Snapshots and the Snapshot Viewer" (http://www.microsoft.com/AccessDev/Articles/snapshot.htm) and "Working with Microsoft Access Objects" (http://msdn.microsoft.com/library/officedev/odeopg/deovrworkingwithmicrosoftaccessobjects.htm).

See Also

Search for "OutputTo method" in Microsoft Access online Help.