how to get the result of a Formula from a cell when using : Open-ExcelPackage -Path "File.xls"

[ericfran]

Hi,
I import an XLS file, one cell contains a formula:

Example:
Style : OfficeOpenXml.Style.ExcelStyle
StyleName : Normal
StyleID : 12
Value : PRY
Text : PRY
Formula : UPPER(LEFT(B2,3))
FormulaR1C1 : UPPER(LEFT(R[-1]C,3))
Hyperlink :
Merge : False
AutoFilter : False
IsRichText : False
IsArrayFormula : False
RichText : {}
Comment :
Worksheet : Data_request
FullAddress : 'Data_request'!B3
FullAddressAbsolute : 'Data_request'!$B$3
ConditionalFormatting : OfficeOpenXml.ConditionalFormatting.RangeConditionalFormatting
DataValidation : OfficeOpenXml.DataValidation.RangeDataValidation
Current :
Address : B3
Start : OfficeOpenXml.ExcelCellAddress
End : OfficeOpenXml.ExcelCellAddress
Table :
IsName : False
Rows : 1
Columns

So Formula: UPPER(LEFT(B2,3))

How to get the result of this formula in my Powershell script ?

Thanks

[dfinke]

This module only supports the latest version of excel files xlsx.

[ericfran]

I mean a file xlsx (so the good format)  … so how should it work ?On 6 May 2023, at 15:00, Doug Finke ***@***.***> wrote: This module only supports the latest version of excel files xlsx. —Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you authored the thread.Message ID: ***@***.***>

[ericfran]

Hi,

found the problem:

• the correct file and with good extension : .xlsx is open (with existing formula)
• different run, debug but file not closed or closed with : Close-ExcelPackage $ExcelWorkBook

In the notes of the module , found this information for formula to use = -Calculate when close the file : Close-ExcelPackage $ExcelWorkBook -Calculate

then open again the file and I have the correct formula result.

Strange that this need to be set and not by default

[dfinke]

At the time the thinking was if there were lots of formulas in a large sheet, the user needs to take an overt action to calculate it.