CODEWITHSUNDEEP
excelfunctionpowershellpowershell-3.0powershell-4.0
GMit
GMit

Reputation: 1

Passing Excel type Variable into PowerShell Function

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.

Upvotes: 0

Views: 946

Answers (1)

TessellatingHeckler
TessellatingHeckler

Reputation: 29033

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)
{
...

Upvotes: 1

Related Questions