worksheet dimension not populating

[ScriptyHarry]

Windows 11 24H2 Powershell 7.4.6 or Windows PowerShell 5.1 ImportExcel 7.8.9

I've got a source file, to which I am adding two pivot tables. The pivot tables are added to their own worksheet., as per default.

$Path = 'C:\Temp\Excel\example.xlsx'

# Open Excel file for editing
$excelPkg = Open-ExcelPackage -Path $Path
# Add PivotTable
Add-PivotTable -ExcelPackage $excelPkg -PivotTableName 'example detail' -SourceWorksheet 'data' -PivotRows 'Date','description' -PivotData @{"number" = "Sum"} -PassThru
# Add PivotTable
Add-PivotTable -ExcelPackage $excelPkg -PivotTableName 'example totals' -SourceWorksheet 'data' -PivotRows 'Date' -PivotData @{"number" = "Sum"} -PassThru

I would like to add some conditional formatting to the worksheet 'example totals'. In order to do that I would like to use the Dimension.Address property on the 'example totals' worksheet, but it is not populated.

# Excel 'Neutral' cell colouring
$YellowStyle = @{
    ForegroundColor = ([Drawing.Color]::FromArgb(156,87,0))
    BackgroundColor = ([Drawing.Color]::FromArgb(255,235,156))
}

PS C:\> Add-ConditionalFormatting -Worksheet $excelPkg.'example totals' -Address $excelPkg.'example totals'.Dimension.Address -RuleType Expression -ConditionValue '=$B3<6' @YellowStyle -Bold
Add-ConditionalFormatting : Cannot bind argument to parameter 'Address' because it is null.
At line:1 char:74
+ ... totals' -Address $excelPkg.'example totals'.Dimension.Address -RuleTy ...
+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidData: (:) [Add-ConditionalFormatting], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Add-ConditionalFormatting

For verification I tried:

Get-ExcelSheetDimensionAddress -Worksheet $excelPkg.'example totals'

and

PS C:\> $excelPkg.'example totals'

Index                 : 3
AutoFilterAddress     :
View                  : OfficeOpenXml.ExcelWorksheetView
Name                  : example totals
Names                 : {}
Hidden                : Visible
DefaultRowHeight      : 15
CustomHeight          : False
DefaultColWidth       : 9,140625
OutLineSummaryBelow   : False
OutLineSummaryRight   : False
OutLineApplyStyle     : False
TabColor              : Color [Empty]
CodeModule            :
WorksheetXml          : #document
Comments              : {}
HeaderFooter          : OfficeOpenXml.ExcelHeaderFooter
PrinterSettings       : OfficeOpenXml.ExcelPrinterSettings
Cells                 : {}
SelectedRange         : {}
MergedCells           : {}
Dimension             :
Protection            : OfficeOpenXml.ExcelSheetProtection
ProtectedRanges       : {}
Drawings              : {}
SparklineGroups       : {}
Tables                : {}
PivotTables           : {example totals}
ConditionalFormatting : {}
DataValidations       : {}
BackgroundImage       : OfficeOpenXml.ExcelBackgroundImage
Workbook              : OfficeOpenXml.ExcelWorkbook

If I close the package and open it again, the dimension is still not populated.

Close-ExcelPackage -ExcelPackage $excelPkg
$excelPkg = Open-ExcelPackage -Path $Path
$excelPkg.'example totals'

If I open the file in excel with Start-Process and close it using Stop-Process, dimension is still not populated

Close-ExcelPackage -ExcelPackage $excelPkg
Start-process $path
Start-Sleep -Seconds 2
Get-Process -ProcessName excel | Stop-Process
$excelPkg = Open-ExcelPackage -Path $Path
$excelPkg.'example totals'

The only way is got dimension to populate was to actually open the file from file explorer with excel and then closing it. Any guidance would be much appreciated.

[dfinke]

Thanks @ScriptyHarry for working with ImportExcel. I have not encountered that. I would need a minimum script that repros this so I could look at it.

[ScriptyHarry]

This is the exact code i'm using:

$Path = 'C:\Temp\Excel\example.xlsx'

# Open Excel file for editing
$excelPkg = Open-ExcelPackage -Path $Path
# Add PivotTable
Add-PivotTable -ExcelPackage $excelPkg -PivotTableName 'example totals' -SourceWorksheet 'data' -PivotRows 'Date' -PivotData @{"number" = "Sum"} -PassThru
# Add Conditional Formatting
# Excel 'Neutral' cell colouring
$yellowStyle = @{
    ForegroundColor = ([Drawing.Color]::FromArgb(156,87,0))
    BackgroundColor = ([Drawing.Color]::FromArgb(255,235,156))
}
Add-ConditionalFormatting -Worksheet $excelPkg.'example totals' -Address $excelPkg.'example totals'.Dimension.Address -RuleType Expression -ConditionValue '=$B3<6' @yellowStyle -Bold
# Close file
Close-ExcelPackage -ExcelPackage $excelPkg

example.xlsx

[dfinke]

I'll take a look - in the meantime try Close-ExcelPackage -Calculate

[ScriptyHarry]

I'll take a look - in the meantime try Close-ExcelPackage -Calculate

Tried using the -Calculate parameter, no change I am sorry to say.

[dfinke]

After thinking about it, my guess is - pivot tables get rendered only after it is opened in Excel. So, getting the dimension from an unopened xlsx won't work.

[ScriptyHarry]

I was afraid of that. Thanks for looking into it