CODEWITHSUNDEEP
excelpowershellpowershell-4.0comobject
TheRob87
TheRob87

Reputation: 146

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.

Upvotes: 1

Views: 78

Answers (1)

mclayton
mclayton

Reputation: 10105

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.

Upvotes: 2

Related Questions