excelpowershellpowershell-4.0comobject

POWERSHELL jump Back in EXCEL COM-Object


I have a function to open a Excel sheet and one to manipulat differend cells in a excel sheet. Now I'd like to save and close the the Excel-data.

But I pass only the Sheet object. How can I jump "back" to the workbook and the apllication object?

My Code:

function OpenExcelTablle{
param(
    [Parameter(Position=1,mandatory=$true)]
    [string] $pwdPath,
    [Parameter(Position=0,mandatory=$true)]
    [string] $file
    )

#Program definieren
$excel = New-Object -ComObject Excel.Application
#password auslesen
#Passwort für Exceldatei einlesen
$password = get-content -Path $pwdPath
#im hintergrund ausführen
$excel.visible = $false
#Datei öffnen
$workbook = $excel.workbooks.open($file,3,0,5,$password,$password)
$blatt= [int](Get-Date -Format "MM")+2
return $workbook.Worksheets.Item($blatt)

 }



function DatenSchreiben{
param(
    [Parameter(Position=1,mandatory=$true)]
    [pscustomobject] $data,#vom Typ CreateAZNObj
    [Parameter(Position=0,mandatory=$true)]
    [__ComObject] $excelTab
    )

    $data.ZeilNum = [int](Get-Date -Format "dd")+5
    $excelTab.Cells.Item($data.ZeilNum,$data.spNum) = $data.Eintrag
 }



function ExExit{
param(
    [Parameter(Position=0,mandatory=$true)]
    [__ComObject] $excelTab
    )

    
$workbook.save()
$workbook.close()
$excel.Quit()
    
 }

The last function I don't know how to realize. Thanks for support.


Solution

  • You're in luck because your $excelTab is a Worksheet object and that has a Parent property that returns the Workbook object that it belongs to.

    Further, a Workbook object also has an Application property that returns a reference to the Application object which it is open in.

    So, your code would become:

    function ExExit
    {
        param(
            [Parameter(Position=0,mandatory=$true)]
            [__ComObject] $excelTab
        )
        
        $workbook    = $excelTab.Parent
        $application = $workbook.Application
    
        $workbook.Save()
        $workbook.Close()
    
        $application.Quit()
        
    }
    

    Workaround

    If you were traversing an object model that didn't happen to have these "upward" parent navigation properties your OpenExcelTablle would need to return the references in a structured object - something like:

    function OpenExcelTablle{
    param(
        [Parameter(Position=1,mandatory=$true)]
        [string] $pwdPath,
        [Parameter(Position=0,mandatory=$true)]
        [string] $file
        )
        #Program definieren
        $excel = New-Object -ComObject Excel.Application
        #password auslesen
        #Passwort für Exceldatei einlesen
        $password = get-content -Path $pwdPath
        #im hintergrund ausführen
        $excel.visible = $false
        #Datei öffnen
        $workbook = $excel.workbooks.open($file,3,0,5,$password,$password)
        $blatt= [int](Get-Date -Format "MM")+2
        $worksheet = $workbook.Worksheets.Item($blatt)
    
        return [pscustomobject] @{
            "Application" = $excel
            "Workbook"    = $workbook
            "Worksheet"   = $worksheet
        }
    
     }
    

    and then the calling code would be able to reference each level in the hierarchy as needed.