VBScript Converter - Overview

The VBScript Converter converts VBScript programs or snippets to the functional equivalent in Perl. This makes it possible to translate calls to COM objects in VBScript to Perl, and makes building Perl programs for the Win32 environment easier.

Intended Uses

Three common uses are:

• Generating Perl code from VBScript examples on the web.

• Automating migration of VBScript programs to Perl code.

• Converting snippets of Visual Basic for Applications (VBA) code generated by macro recorders in Microsoft Office applications (Word, Excel, etc) to Perl code for use in a separate application. Note that VBA-to-Perl translations may not be as precise as VBScript-to-Perl. It is recommended that you test all converted code before inserting it in an application.

Why Convert VBScript to Perl?

Advantages to converting VBScript programs to Perl include:

• access to Perl's richer data structures, pattern-matching, and built-in functions

• access to the CPAN code repository of Perl modules

• code that can invoke both Win32 and .NET objects using PerlNET

• greater ease of debugging Perl code compared to VBScript

Using VBScript Converter

Graphical Interface:

The VBScript Converter graphical interface can be opened by launching VBScript Converter from the Windows Start menu, or running vbsperl.exe without arguments from the command line. The graphical interface is described in detail in the VBScript Converter Graphical Interface document.

Command Line:

VBScript Converter can be run at the command line. Specify the file containing the input code as an argument to the vbsperl command. For example:

  vbsperl inputfile.vbs

Output from the conversion will be written to the console session. Use standard pipe and redirect syntax to write output to a file. For example:

  vbsperl inputfile.vbs > outputfile.pl

See the VBSPerl manpage for more information on command line syntax and options.

Windows Clipboard:

VBScript Converter can convert VBScript to Perl in the Windows system clipboard. Running VBScript Converter with the --shortcut option creates a desktop shortcut for the vbsperl --clipboard command.

To use the clipboard for input and output:

1. Copy the the VBScript or VBA code to be converted to the Windows system clipboard. For example, if the code is currently displayed in an editor such as Notepad, highlight the desired section of code and press 'Ctrl' + 'C'.

2. Double-click VBSPerl desktop icon (see above) or run the command 'vbsperl --clipboard'. This feeds the contents of the Windows clipboard to VBSPerl for conversion. When the conversion is complete, a message box is displayed.

3. Paste the output code (e.g. using 'Ctrl' + 'V') into a text editor.

Supported Forms of Visual Basic

There are three types of Visual Basic:

• VBScript: used primarily to script web pages.

• Visual Basic for Applications (VBA): hosted by a supporting application, including Microsoft Office applications. Mainly used as a macro language for scripting the host applications

• Visual Basic: the language and development environment used to build graphical applications rapidly.

VBScript Converter only supports conversion of VBScript and Visual Basic for Applications (VBA).

Type Libraries

All three variants of Visual Basic use Win32 type libraries to determine how to execute the user's code. These type libraries are used to help the interpreter augment the code the user provides. For example, in the following Windows Scripting Host (WSH) example, there is an implicit Item method after the drives accessor that uses the argument.

  set fsObj = GetObject("Scripting.FileSystemObject")
  set driveInfo = fsObj.drives("C")

When the VBScript Converter detects this code, it finds the type library associated with the Scripting.FileSystemObject class, and injects any omitted methods into the expression.

There are some limitations to VBScript Converter conversions. First, it helps to use constants in key places, as opposed to variables. For example, the VBScript Converter currently does not track the type of the fsObj variable in this example:

  progID = "Scripting.FileSystemObject"
  set fsObj = GetObject(progID)
  set driveInfo = fsObj.drives("C")

In this case, the VBScript Converter does not recognize that c<fsObj> is a reference to a COM object. It does not determine that c<drives> is a method returning a COM collection and that it takes a default method called 'item'. The VBScript Converter will recognize "C" as the only argument for the drives method. See the Limitations in VBA Code section below for other conversion limitations.

Associations

VBScript programs contain only ProgIDs, not type libraries. VBA code rarely contains either, as it is usually generated with the assumption that the tool hosting the macro recorder will be the environment the generated code executes in. For example, VBScript code that drives Excel would look like this:

  set obj = CreateObject("Excel.Application")
  obj.Range("A1") = 1
  obj.Range("A2") = 2
  obj.Range("A3") = 3
  obj.Range("A4").FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"

Code generated by the Excel macro recorder does not need the obj variable because VBA supplies the host object for the application of these accessors.

VBA Associations

A type library is required to convert VBA code to Perl code. The VBScript Converter can find the type library in one of two ways.

The first method is to use the registry to find a type library based on the ProgID. This often works, but it fails for some commonly used Windows applications like Microsoft Excel and Word. Because we expect many programs to target these two applications, particularly Excel, we have hardwired their mappings in the code itself.

When the registry doesn't provide a path, VBScript Converter tries using the ProgID itself to find a type library, via pattern matching using the text in the ProgID against the labels of the various type libraries installed in the registry. This can be time-consuming, depending on how deeply VBScript Converter has to search the registry.

VBScript Associations

With VBScript applications, you can provide a file of explicit mappings. The format for this file is:

  ProgID = Pattern label
  # Sample
  ADODB.Connection = Microsoft OLE DB ActiveX Data Objects \d+\.\d+ Library

This mapping is specified by the --progIDMapFile mapFile option. Multiple map files may be specified.

VBA Associations

When VBA code is converted, VBScript Converter needs to determine which class the input code is intended for, and then find the appropriate ProgID and type libraries. VBScript Converter looks at unbound accessors found in the code. For example, the word "range" in the code range("A1") = 1 is an unbound accessor. (Note that VBScript Converter ignores case, but tries to generate the correct case because Perl is case-sensitive while VB is not.) VBScript Converter maintains a small dictionary that maps relatively common, distinctive accessors to widely used ProgIDs, such as mapping "range" to "Excel.Application". Common accessors like "Parent", "Application", and "Document" are not tracked. Currently supported ProgIDs include the following:

  Excel.Application
  ADODB.RecordSet
  ADODB.Connection
  Word.Application
  InternetExplorer.Application
  MSXML3.DOMDocument
  LDAP
  WINMGMTS
  PowerPoint.Application
  WScript.Shell
  MapPoint.Application
  Outlook.Application
  MAPI
  Notes.NotesSession
  Access.Application

When working with code targeted for a different application, or when there are not enough distinctive unbound accessors in the sample code to translate, you can use the --app-progid and --app-typelib options to specify this information.

Sample Program

1. Copy the following VBScript program to the Input pane of the VBScript Converter graphical interface.

     set objRootDSE = GetObject("LDAP://RootDSE")
     strADsPath =  ";"
     strFilter  = "(objectcategory=domainDNS);"
     strAttrs   = "name;"
     strScope   = "SubTree"
     
     set objConn = CreateObject("ADODB.Connection")
     objConn.Provider = "ADsDSOObject"
     objConn.Open "Active Directory Provider"
     set objRS = objConn.Execute(strADsPath & strFilter & strAttrs & strScope)
     objRS.MoveFirst
     while Not objRS.EOF
         Wscript.Echo objRS.Fields(0).Value
         objRS.MoveNext
     wend

2. Click the 'Convert' button. The results of the conversion will appear in the Output pane:

     #!perl
     
     use Win32::OLE;
     
     $objRootDSE = Win32::OLE->GetObject('LDAP://RootDSE');
     $strADsPath = ';';
     $strFilter = '(objectcategory=domainDNS);';
     $strAttrs = 'name;';
     $strScope = 'SubTree';
     
     $objConn = Win32::OLE->new('ADODB.Connection');
     $objConn->{Provider} = 'ADsDSOObject';
     $objConn->Open('Active Directory Provider');
     $objRS = $objConn->Execute($strADsPath . $strFilter . $strAttrs . $strScope);
     $objRS->MoveFirst();
     while (!$objRS->{EOF}) {
         print $objRS->Fields(0)->Item->Value->{Value}, "\n";
         $objRS->MoveNext();
     }

3. Run the script to confirm that it performs as expected or debug as neccessary.

Limitations

Unsupported functions

The following built-in VBScript functions are not yet supported:

  dateadd
  datediff
  datepart
  datevalue
  eval
  execute
  getref
  inputbox
  instrb
  isdate
  loadpicture
  scriptengine
  scriptenginebuildversion
  scriptenginemajorversion
  scriptengineminorversion
  textstream
  timeserial
  timevalue
  vartype
  leftb
  lenb
  midb
  rightb

Eventually, the date-related functions will be rewritten in terms of Perl modules like Date::Calc. Some of these other functions are too specific to either the scripting host runtime or the VBScript environment (e.g. eval) and will never be implemented. When the VBScript converter encounters an unsupported function, a comment is displayed in the generated code, indicating that the function was not converted.

Limitations in VBA Code

Some VBScript code cannot currently be converted to correct Perl code. In particular, unbound accessors are currently not converted in all situations. For example, suppose the Excel macro recorder generates this code:

  Range("A1") = 10
  Range("A2").Select
  ActiveCell = "20"
  Range("A3") = 30
  Range("A3").Select
  MsgBox Range("A3")
  MsgBox ActiveCell

It should be converted to this Perl code:

  $_app_object->Workbooks->Add();
  $_app_object->Range('A1') = 10;
  $_app_object->Range('A2')->Select();
  $_app_object->ActiveCell = '20';
  $_app_object->Range('A3') = 30;
  $_app_object->Range('A4')->{FormulaR1C1} = '=SUM(R[-3]C:R[-1]C)';
  Win32::MsgBox($_app_object->Range('A4'));
  $_app_object->Range('A4')->Select();
  Win32::MsgBox($_app_object->ActiveCell);
  $_app_object->{ActiveWorkbook}->{Saved} = vbTrue;

But it is in fact converted to this code:

  $_app_object->Workbooks->Add();
  $_app_object->Range('A1') = 10;
  $_app_object->Range('A2')->Select();
  $ActiveCell = '20';
  $_app_object->Range('A3') = 30;
  $_app_object->Range('A4')->{FormulaR1C1} = '=SUM(R[-3]C:R[-1]C)';
  Win32::MsgBox($_app_object->Range('A4'));
  $_app_object->Range('A4')->Select();
  Win32::MsgBox($ActiveCell);
  $_app_object->{ActiveWorkbook}->{Saved} = vbTrue;

The code translator has yet to be upgraded to handle a greater variety of cases.