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 (noSet
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
statementdebug.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 whenCmdletBinding()
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]