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.
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()
}
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.