Im trying to append TXT to XLSX based on patterns. If the pattern matches with the column it should append. It works when I define the sheet columns and items explicitly. Example:
$sheet.cells.item($row, $Date.Column).value = $txtDate
I want to find and map the patterns to columns implicitly so I don't define the columns manually. However, I get the following error: HRESULT: 0x800A03EC. What's going wrong here?
$fileContent = @'
Node: ABC
Name: PC
Cluster: HS1
Node: XZZ
Name: CC
Cluster: HS2
Node: XYZ
Name: DD
Cluster: HS3
'@
$Testbestand = $fileContent.Split( [System.Environment]::NewLine,
[System.StringSplitOptions]::RemoveEmptyEntries )
$linieIdLast = ''
$linieTemplate = [ordered]@{}
foreach ( $linie in $Testbestand ) {
$linieId, $linieVal = $linie -split ":\s*",2
if ( $linieId -in $linieTemplate.Keys ) {
break
} else {
$linieIdLast = $linieId
$linieTemplate.Add( $linieId, $Null )
}
}
$linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
$liniesAll = foreach ( $linie in $Testbestand ) {
$linieId, $linieVal = $linie -split ":\s*",2
$linieComb.$linieId = $linieVal.Trim()
if ( $linieId -eq $linieIdLast ) {
$linieComb
$linieComb = New-Object -TypeName PSCustomObject -Property $linieTemplate
}
}
$TargetFile = Targetfile
$sheetName = Sheet
$objExcel = New-Object -ComObject Excel.Application
$workBook = $objExcel.Workbooks.Open($TargetFile)
$sheet = $workBook.sheets.item($sheetName)
$row = 33
foreach ( $linieKey in $linieTemplate.Keys )
{
$column = ($sheet.Columns.Find($linieKey))
$sheet.cells.item($row, $column).value = $linieTemplate.$linieKey
$row++
}
$workBook.Save()
$objExcel.Quit()
Error:
At line:47 char:8
+ $sheet.cells.item(1, $column).value = $linieTemplate.$linieKey
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OperationStopped: (:) [], COMException
+ FullyQualifiedErrorId : System.Runtime.InteropServices.COMException
Aucun commentaire:
Enregistrer un commentaire