Helper VBScript library

Summary: Common utilities for VBScript files.

I'm not a fan of VBScript, but I have been using it extensively for writing utility scripts. In this post, I want to share one technique that I found helpful when working with VBScript files.

The scripts I write share many functions, such as processing command-line parameters, connecting to SQL Server, and so on. To make my life easier, I combined these common functions in a reusable helper file. I called this helper file Common.vbs. Here are some of the most helpful functions implemented in Common.vbs:

• GetParamValue: Returns the value of the specified parameter for a given command-line switch; command-line switches are case insensitive and support name variations, such as "h|help|?" (i.e. /h, /help, and /? are considered identical).
• GetParamValues: Returns an array of values for the specified command-line parameter (command-line switches use the format {/|-}switch[:Value1[,Value2[...]]], such as /in:c:\data1.txt,c:\data2.txt).
• GetFile: Prompts user to select a file using the standard File Open dialog box.
• GetSecretValue: Prompts user to enter a password (or any other sensitive value) and masks the entered characters.
• RevertToCscript: Forces script to be run by the CSCRIPT engine (instead of WSCRIPT).
• SqlConnect: Opens connection to SQL Server via OLE DB provider (supports Windows authentication).
• ExcelConnect: Opens an ADO database connection to an Excel file.
• GetComError: Generates formatted error message retrieved from the Err object.
• GetExcelWorksheetName: Returns the name of the first worksheet in the Excel file.

You can find the detailed description of these and other methods in the file comments. Use the following link to get the Common.vbs file:

• Download Common.vbs

To reference the Common.vbs file from another script, implement your script as a Windows Script File (.wsf). Unlike regular VBScript (.vbs) files, .wsf files can reference other VBScript files (you must specify the location of the referenced file using either an absolute or relative path).

To implement a script as a .WSF file, just wrap you VBScript code in XML elements and add a <script> element referencing the Common.vbs file, such as:

<package>
<job ID="SomeID">
<script Language="VBScript" Src="Common.vbs" />
<script Type="text/vbscript">
' Your VBScript logic goes here.
</script>
</job>
</package>

You can execute a .wsf file via the CSCRIPT (or WSCRIPT) command as if it were a .vbs file.

See also:
Windows Script Host
VBScript