Unable to read the result of a formula

[JeanYve]

Hello,

I can't read the result of this formula: =SIERREUR(INDEX(INDIRECT($H12;VRAI);EQUIV(B12;INDIRECT(TEXTE($H12&W$1;)));2);"")
I should point out that it's only ImportExcel that can't read the result. Excel is able to obtain the result correctly.

Here is my code for reading:
$royaltiesTotal = $worksheetRoyalties.Cells[$rowIndex, $colRoyaltiesTotal].Value
Write-Host ".Value = $royaltiesTotal"
$royaltiesTotal = $worksheetRoyalties.Cells[$rowIndex, $colRoyaltiesTotal].Formula
Write-Host ".Formula = $royaltiesTotal"
$royaltiesTotal = $worksheetRoyalties.Cells[$rowIndex, $colRoyaltiesTotal].Text
Write-Host ".Text = $royaltiesTotal"

The result is :

But in the Excel file I can see the result: 0.03

For your information, I do have a:
Close-ExcelPackage $firstExcelPackage -Calculate -SaveAs $exportFilePath
$firstExcelPackage.Dispose()
$secondExcelPackage = Open-ExcelPackage -Path $exportFilePath
before reading my data.

No problem with other formulas.

Do you have a solution?
Thank you in advance

[dfinke]

do you have an xlsx you can share that has a simple repro?

[JeanYve]

Of course, here it is :
ROYALTIES_280_11_2024.xlsx

In the “Calcul de Royalties” sheet, I'm trying to read the “% Royalties Total” column. I'm also trying to read “Royalties Total” which has a formula based on the “% Royalties Total” column.

If you need an Excel file with more real data, I'll send it to your Hotmail address, but I think the one here is sufficient.

Thank you for your help

[dfinke]

Need a simple one.

For example, single sheet, single cell (or a simple row) that demonstratest the problem.

Alng with the actual PowerShell that tries to read the cell and does not five you the answer you expect.

[JeanYve]

In fact, I've just found the problem. When I try to read the data in the Excel file just created by my script, it doesn't work.
However, if once my script has created the Excel file, I manually open the file AND save it, then I can read the data.

Is it possible to automate this opening and saving via PowerShell?

[dfinke]

try Close-ExcelPackage -Calculate

[JeanYve]

I've already tried. To no avail.
It doesn't matter, the workaround I've found works for me.
Thanks anyway for your help

[dfinke]

The workaround was, launch it in Excel and save?