Passing Excel type Variable into PowerShell Function

Question

I'm basically trying to create a Power Shell function which takes in a variable set to an excel worksheet and returns the last used row and column.

I know the code works outside of a function. Here's what it looks like in my function.

Function FindRange ([string] $path)
{
 $mainRng = $path.UsedRange.Cells 
 $ColCount = $mainRng.Columns.Count 
 $RowCount = $mainRng.Rows.Count  
 $xRow = $RowCount
 $xCol = $ColCount
 Write-Host "function DEBUG xrow:" $xRow
 Write-Host "function DEBUG xcol: " $xCol
 Write-Host "function path:" $path
}
FindRange $BTWSRawSheets

$BTWSRawSheets is:

 $BTWSRawSheets = $wbBTWS.worksheets | where {$_.name -eq "Export Worksheet"}

The output of Write-Host "function path:" $path is

 function path: System.__ComObject

Is anyone able to help me figure out how to pass the excel worksheet in as the parameter? I've spent a few hours researching and trying but have had no luck.

TessellatingHeckler · Accepted Answer · 2016-05-13 03:26:25Z

1

I think you're mixing up whether it's a spreadsheet filename (a text string, $path is a reasonable name for it) or whether it's a live COM object of a running Excel instance, connected to a worksheet. Given the way you use $path.UsedRange directly, and from the output message, I guess it's supposed to be the latter one - so the way you are casting the parameter to a [string] is breaking it.

Try removing the [string] type:

Function FindRange ($path)
{
...

answered May 13, 2016 at 3:26

TessellatingHeckler

29.3k4 gold badges55 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing Excel type Variable into PowerShell Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related