six demon bag

Wind, fire, all that kind of thing!

2015-01-12

Translate VBA to PowerShell

Microsoft Office applications can be controlled from PowerShell in the same way they can be controlled from VBScript. Most of what was said in the previous article about translating VBA to VBScript applies to PowerShell as well, but there are some additional things to take care of due to the syntactical differences between PowerShell and the VB dialects.


  • As in VBScript you need to create an instance of your Office application first. In PowerShell COM objects are created via New-Object -ComObject, and objects are assigned to variables just like primitive data types (no Set keyword).

    $xl = New-Object -ComObject 'Excel.Application'

    Beware that Office applications do not automatically terminate when the script ends. You need to manually terminate them:

    $xl.Quit()
    

    You also need to dispose the object, so it doesn't keep lingering in memory:

    [Runtime.InteropServices.Marshal]::ReleaseComObject($xl)
    [GC]::Collect()
    [GC]::WaitForPendingFinalizers()
    
  • Methods cannot be called with just their name like they can in the VB dialects:

    wb.Close
    xl.Quit
    

    They must be called with a parameter list in parentheses (even if the list is empty):

    $wb.Close()
    $xl.Quit()
  • There are no implicit objects in PowerShell. You must have a handle to each object whose methods or properties you want to access. For instance, a VBA statement

    ActiveWorkbook.Close
    

    implicitly uses the Application object, making this the effective statement:

    Application.ActiveWorkbook.Close
    

    In PowerShell you need to explicitly provide the application object you created before:

    $xl.ActiveWorkbook.Close()
  • There are no default methods in PowerShell, so items in collections can't be accessed using the abbreviated notation

    ActiveWorkbook.Sheets(2).Cells(3, 4)

    Instead you must use the Item property of the collection:

    $xl.ActiveWorkbook.Sheets.Item(2).Cells.Item(3, 4)
  • PowerShell can use VBA constants via the Microsoft.Office.Interop namespace:

    Add-Type -Assembly 'Microsoft.Office.Interop.Excel'
    $xl.Selection.VerticalAlignment = [Microsoft.Office.Interop.Excel.Constants]::xlTop

    You can download redistributable packages of the Interop assemblies for Office 2003 and Office 2007 in the Microsoft Download Center.

    If the Interop assemblies are not available to you for some reason, you can still define a constant yourself:

    Set-Variable -Name xlTop -Value -4160 -Option Constant
    $xl.Selection.VerticalAlignment = $xlTop

    or use the numeric value:

    $xl.Selection.VerticalAlignment = -4160
  • PowerShell doesn't support named arguments in method calls. Instead of

    ActiveWorkbook.SaveAs Filename:="some.xlsx", FileFormat:=xlWorkbookDefault, CreateBackup:=False

    you have to provide all arguments in the correct order. Use [Type]::Missing for optional arguments that you want to leave at default values. A trailing list of default arguments may be left out completely:

    $def = [Type]::Missing
    $xl.ActiveWorkbook.SaveAs('some.xlsx', 51, $def, $def, $def, False)

While the above should cover what you'll normally encounter with recorded macros, there are some other notable differences that you may have to deal with when translating hand-written macros, particularly:

  • Instead of

    Option Explicit
    

    use

    Set-PSDebug -Strict
    
  • PowerShell doesn't have typed variables (variables can contain values of any type), and also doesn't use the Dim keyword for defining variables. You need to change definitions like this:

    Dim foo As String

    into something like this:

    Set-Variable -Name foo
    

    or (shorter) like this:

    $foo = $null
    
  • The PowerShell equivalent for the debug.Print statement

    debug.Print "some text"

    is the Write-Debug cmdlet:

    Write-Debug 'some text'

    That cmdlet writes to the debug output stream when $DebugPreference is set to 'Continue' (default is 'SilentlyContinue', in which case debug output is suppressed). The variable can be set either manually or via the -Debug common parameter when CmdletBinding() is enabled in your script or function.

  • Error handling in PowerShell is different from VBA. You can't jump to line labels on errors. Instead of

    On Error Goto LABEL
    'do things that might fail
    On Error Goto 0
    
    
    '
    'more code
    '
    
    
    LABEL:
    'error handling routine
    Resume

    you'll wrap PowerShell code in a try..catch statement:

    try {
      # do things that might fail
    } catch [SomeException] {
      # error handling for SomeException
    } catch [OtherException] {
      # error handling for OtherException
    } catch {
      # error handling for all other exceptions
    } finally {
      # cleanup routines
    }
    
    
    #
    # more code
    #

    Note that PowerShell distinguishes between terminating and non-terminating errors. The latter are not caught by try..catch, although the error message is still printed to the error output stream. You can turn errors into terminating errors by calling a cmdlet with the common parameter -ErrorAction Stop:

    Get-Foo -ErrorAction Stop
    

    or by setting $ErrorActionPreference to 'Stop'.

    $ErrorActionPreference = 'Stop'
    Get-Foo
    

Posted 16:30 [permalink]