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

[dfinke]

The workaround was, launch it in Excel and save?

[thesupporthero]

Can I piggy back off this?
I have to import an automatically generated excel file, that has a ton of formulas.
I have the same problem where the file doesnt come with the formulas preran. To make it more fun the folder I am pulling from will be read only...which is fine. That said, when I go to close-excelpackage -calculate, I get a generic error that it was unable to calculate due to links being broken(which technically is true..I guess they dont always have date for some entries). while it does save, it doesnt update the formulas.
And of course works fine in excel itself...sadly I can't install excel on the server.

Is there a way to maybe force calculations?

[dfinke]

thanks @thesupporthero - the underlying process calls into code that I don't own to calculate.

[thesupporthero]

thanks @thesupporthero - the underlying process calls into code that I don't own to calculate.

Gotcha, Thanks for the reply. do you know of a workaround/another way to calculate on load?

I am going to have to just install excel on the batch server arent I? LOL

[dfinke]

I think so 😄.

The EPPlus version is an older one, don't know if the newer one has a better "Calculate" method. They have a version 5 but it has a license cost and and numerous breaking changes and I don't support the new version.

[dmoroney]

I had a similar problem with referenced formulas. I solved it by assigning the search criteria to a cell and then writing each formula that uses that search criteria. I can see the results immediately on close.

$Excel = Open-ExcelPackage -Path "some xlsx"
. . .
Set-ExcelRange -Address $Sheet2.Cells["A2"] -Value "string"
Set-ExcelRange -Address $Sheet2.Cells["B2"] -Formula "=COUNTIF(Sheet1!B:B, A2)"
Set-ExcelRange -Address $Sheet2.Cells["C2"] -Formula "=SUMIF(Sheet1!B:B, A2, Sheet1!F:F)"
Set-ExcelRange -Address $Sheet2.Cells["D2"] -Formula "=SUMIF(Sheet1!B:B, A2, Sheet1!G:G)"
. . .
Close-ExcelPackage $Excel -Show