Manipulate a COM object across multiple functions in powershell

Question

I am creating a library of powershell functions to manipulate excel (find cell with matching value in collection, copy range, export sheet as collection etc.). I need to be able to pass in parameters and return a datatable so these functions can interact with BluePrism via C#/VB. I am testing calling these functions from BluePrism but whenever I leave a function it breaks my connection to the excel object.

I've been using functions to manipulate an excel COM object and this works as long as 1) my functions are in the same script 2) my access modifiers are set to script. However, I want to be able to call these functions individually from BluePrism.

When I break out of the initializing function (that activates the worksheet/workbook/etc), it breaks my connection to excel.

Is there a way to reconnect to a COM object or somehow keep it active? If not, what other approaches could I use manipulate excel across multiple functions?

Below is an example of two functions I'm trying to run, one that opens and activates an excel sheet and one that returns a particular cell value:

function Open_ExcelSheet{
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [hashtable]
        $parms
    )
    process{
        try{
            $excelPath = if($null -ne $parms.excelPath) {$parms.excelPath} else {throw "No path specified"}
            $sheetName = if($null -ne $parms.sheetName) {$parms.sheetName} else {throw "No sheet specified"}
            $visible = if($null -ne $parms.isVisible) {$parms.isVisible} else { $false }
                <#Load excel file and navigate to sheet#>
            $excel = New-Object -ComObject Excel.Application
            if($visible -eq $true){
                $excel.Visible = $True
            }
            $workbook = $excel.Workbooks.Open($excelPath)
            $worksheet = $workbook.Worksheets.Item($sheetName)
            $worksheet.Activate()

            return "True"
        }
       catch{
            return "False"
       }
    }




function Get_ExcelCell{
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [hashtable]
        $parms
    )
    process{
        try{
            $excelPath = if($null -ne $parms.excelPath) {$parms.excelPath} else {throw "No path specified"}
            $sheetName = if($null -ne $parms.sheetName) {$parms.sheetName} else {throw "No sheet specified"}
            $searchValue = if($null -ne $parms.searchValue){$parms.searchValue} else {throw "No search value specified"}
                <#Load excel file and navigate to sheet#>
            $workbook = $excel.Workbooks.Open($excelPath)
            $worksheet = $workbook.Worksheets.Item($sheetName)
            $worksheet.Activate()        
                    
            $Found = $worksheet.Cells.Find($searchValue)
                
            $cellInfo = [PSCustomObject]@{
                Row = $Found.Row()
                Column = $Found.Column()
                Value = $Found.Value()
                }

            $a = $cellInfo | ConvertTo-DbaDataTable -EnableException
            $cellInfo | ConvertTo-DbaDataTable -EnableException -OutVariable dt
            return "True"
        }
        
        catch {
            return "False"
        }
    }
        
}

Please add some example of what you tried, even if it's just what you want it to look like. It sounds like you just want to set up some functions while being careful of scopes, and just import them into your main blueprism task to use as needed? If you need to start separate powershell instances though, it's just an issue of connecting COM to an already-running excel — Cpt.Whale, Feb 02 '23 at 19:47
I've added my initial connection function and then a function that's supposed to get a value from the sheet. Ideally yes I can call each function as needed since there are several more "Get" type functions I need to use. I am not exactly sure what the most efficient way to do this would be since I'm new to Powershell. It sounds like your idea of connecting com to an open excel would take care of this but I wasn't sure how to do that and had trouble finding relevant answers. — szwells98, Feb 02 '23 at 22:55

score 0 · Answer 1 · answered Feb 03 '23 at 01:53

Got it. I stole the solution from this question: How to connect to existing instance of Excel from PowerShell?

Essentially this line connects to the existing excel: $excel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')

Here's what it looks like in context. There are some other code tweaks because this is what I actually used in context that worked:

function Open_ExcelSheet{
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [hashtable]
        $parms
    )
    process{
        try{
            $excelPath = if($null -ne $parms.excelPath) {$parms.excelPath} else {throw "No path specified"}
            $sheetName = if($null -ne $parms.sheetName) {$parms.sheetName} else {throw "No sheet specified"}
            $visible = if($null -ne $parms.isVisible) {$parms.isVisible} else { $false }
                <#Load excel file and navigate to sheet#>
            
            $excel = New-Object -ComObject Excel.Application
            if($visible -eq $true){
                $excel.Visible = $True
            }
            $workbook = $excel.Workbooks.Open($excelPath)
            $worksheet = $workbook.Worksheets.Item($sheetName)
            $worksheet.Activate()
            

            return "True"
        }
       catch{
            return "False"
       }
    }

}




function Get_CellByValue{
    [CmdletBinding()]
    param (
        [Parameter(Mandatory)]
        [hashtable]
        $parms
    )
    process{
        try{
            $excelPath = if($null -ne $parms.excelPath) {$parms.excelPath} else {throw "No path specified"}
            $sheetName = if($null -ne $parms.sheetName) {$parms.sheetName} else {throw "No sheet specified"}
            $searchValue = if($null -ne $parms.searchValue){$parms.searchValue} else {throw "No search value specified"}
            $visible = if($null -ne $parms.isVisible){$parms.isVisible} else {$false}
                <#Load excel file and navigate to sheet#>
           
            $excel = [Runtime.Interopservices.Marshal]::GetActiveObject('Excel.Application')
            $workbook = $excel.Workbooks.Open($excelPath)
            $worksheet = $workbook.Worksheets.Item($sheetName)
            $active = $worksheet.Activate()        
            
            if($active -eq $false){
                return "Ow"
                break
            }

            $Found = $worksheet.Cells.Find($searchValue)
                
            $cellInfo = [PSCustomObject]@{
                Row = $Found.Row()
                Column = $Found.Column()
                Value = $Found.Value()
                }

            $a = $cellInfo | ConvertTo-DbaDataTable -EnableException
            $cellInfo | ConvertTo-DbaDataTable -EnableException -OutVariable dt
            return "True"
        }
        catch{
            return "False"
        }
    }
}

Manipulate a COM object across multiple functions in powershell

1 Answers1