six demon bag

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]

Archives