How to pass optional arguments to object method using PowerShell?

Question

I would like to call the method of an object. This method has many optional parameters but I would like to use only one. How can I do that in PowerShell ?

Example: Object Workbooks has method OpenText (link) which has many optional parameters but I would like to pass only the value for parameter DecimalSeparator, all others I don't want to specify.

In other languages I would do something like this:

$App.Workbooks.OpenText($path, DecimalSeparator=$decSeparator)

Please, tell me how to do that in PowerShell ?

Have you tried, or are yo saying you did and it did not work? If an API, or DOM requires a param or multiple params to be passed, even if it is blank/null, then it has to be passed. PowerShell does not control that. You have provided the link to the DOM / method, and it should specifically state what it will and will not allow. A quick look at the article, says only one param is required and all others are optional. Nothing in the linked doc states that they are positional — postanote, Mar 11 '19 at 07:34
THis may not be possible. See https://stackoverflow.com/a/17484660/67392 — Richard, Mar 11 '19 at 07:36

score 0 · Answer 1 · answered Mar 11 '19 at 07:43

Have you tried, or are yo saying you did and it did not work?

If an API, or DOM requires a param or multiple params to be passed, even if it is blank/null, then it has to be passed. PowerShell does not control that.

You have provided the link to the DOM / method, and it should specifically state what it will and will not allow.

A quick look at the article, says only one param is required and all others are optional. Nothing in the linked doc states that they are positional, other that the required one. So, you can pass the in any order or as many / as few as you'd like.

The easiest way to come to terms regarding if you can do X or Y in Office docs, is to open a file in Office, enable the Macro Recorder, do the steps in the GUI as normal, allowing the recorder to capture all actions, then look at the resulting VBA and convert that for use in PowerShell.

Converting VBA for use in PowerShell is a well worn Q&A.

Convert code from Excel Macro to PowerShell

   lrow = Range("G" & Rows.Count).End(xlUp).Row
    Set MR = Range("G2:G" & lrow)
    For Each cell In MR
        If UCase(Trim(cell.Value)) = "FALSE" Then
            cell.Interior.ColorIndex = 3
        End If
    Next


$objExcel = New-Object -ComObject Excel.Application

$objExcel.Visible = $true
$objExcel.DisplayAlerts = $false

$filePath = "c:\logs\2015-04-23.csv"

$xlsFilePath = Get-Item -Path $filePath | % { Join-Path (Split-Path $_ -Parent) "$($_.BaseName).xls" } 

$workBook = $objExcel.Workbooks.Open($filePath)
$workSheet = $WorkBook.sheets | select -First 1

$xlup = -4162

$lrow = $workSheet.cells.Range("G" + $workSheet.Rows.Count).End($xlup).Row

$workSheet.cells.Range("G2:G" + $lrow) | % { 
    $value = $_.Text
    if($value.ToUpper() -eq "TRUE"){
       $_.Interior.ColorIndex = 3 
    }
}

$WorkBook.SaveAs($xlsFilePath, 18) 
$objExcel.Quit()

I only put this here as a reminder, but based on your post snippet, I am going to assume you knew this already. Yet, it's a just in case.

How to pass optional arguments to object method using PowerShell?

1 Answers1