Weird formula assignment

[RoYoMi]

Happy Holidays everyone :)

I am populating a table cell with a formula, but the formula stored in the file magically gets a new character which then breaks the formula.

Using ImportExcel v7.8.10
Using PowersShell v7.4.12
Excel version from Microsoft 365 enterprise

The worksheet was defined as a table with fields for Action Add, UserName, Address, Name, and NewName

Here is the MVP pattern I'm using:

$BlankRecordForFile = [PsCustomObject]@{
    'Action Add'    = ''
    UserName        = ''
    Address         = ''
    Name            = ''
    NewName         = ''
    }

$File = [system.io.fileinfo]'c:\myfolder\myfile.xlsx'

$ExportOptions = @{
    Path            = $File.FullName
    WorksheetName   = 'Data'
    TableName       = 'DataTable'
    TableStyle      = 'Light1'
    AutoSize        = $True
    AutoFilter      = $True
    BoldTopRow      = $True
    FreezeTopRow    = $True
    StartRow        = 1
    }
$ExcelFile  = $BlankRecordForFile | Export-Excel @ExportOptions -PassThru

$Worksheet  = $ExcelFile.Workbook.Worksheets[ 'Data' ]

$Worksheet.InsertRow( 2, 1 )

# Note:                                no '@' character between these arrows --->  <---
$Formula = '=IFS( [[#This Row],[UserName]]="","", [[#This Row],[Action Add]]=TRUE, CONCAT([[#This Row],[Address]],"-",[[#This Row],[UserName]]), CONCAT([[#This Row],[Address]],"-",[[#This Row],[UserName]]) <> [[#This Row],[Name]], CONCAT([[#This Row],[Address]],"-",[[#This Row],[UserName]]), TRUE, "")'

$Cell = $Worksheet.Cells[ 'e2' ]
$Cell.Formula = $Formula
Write-Host "Assigned formula '$( $Cell.Formula )'"

Close-ExcelPackage $ExcelFile

$Cell.Formula shows the exact value I provided.

When viewing the formula in Excel we can see the resulting formula is changed to this:

=@IFS( [@UserName]="","", [@[Action Add]]=TRUE, CONCAT([@Address],"-",[@UserName]),@ CONCAT([@Address],"-",[@UserName]) <> [@Name], CONCAT([@Address],"-",[@UserName]), TRUE, "")

• the initial @ injected after the equals sign, this is a new thing Excel is doing. It's strange but is apparently normal
• the [#This Row], are replaced with @ to make the table references work. This is a desired change.
• a @ symbol injected at character 84 directly before CONCAT. This extra character breaks the formula. This symbol was not present in my original formula. In Excel if I remove this character the formula starts to work as expected.

I'm looking for advise. Is this a real bug; or am I just doing it wrong; or is there a better way to do this?

[dfinke]

Thanks for reporting the issue @RoYoMi. Off the top of my head it looks fine. I'd have to get in there and try some scenarios.
I vaguely remember issues with formulas, maybe with the @ getting added. I don't remember the details or if there was a fix/workaround etc.

You have a good harness here. My first approach would be to start with the simplest formula that works and then stepwise add complexity to see where and when it breaks.

Should be quick to iterate on it and get some ideas.

I'm going to assign copilot to these see if comes up with anything useful.

[dfinke]

@RoYoMi Copilot came up with an interesting approach. I tried but it did not work.
I did not know there was a FullCalcOnLoad setting.

If I or someone else comes up with something, will revisit.

Sorry.

[scriptingstudio]

Could not figure out what your formula does but could get rid of @ symbol before CONCAT
My comment at [1425]: #1425 (comment) also could be helpful

1. Remove = before IFS
2. Replace (or comment) line $Cell.Formula = $Formula with

Set-ExcelRange -Worksheet $Worksheet -Range E2 -ArrayFormula -Formula $Formula

Result in Excel

=IFS( [@UserName]="";""; [@[Action Add]]=TRUE; CONCAT([@Address];"-";[@UserName]); CONCAT([@Address];"-";[@UserName]) <> [@Name]; CONCAT([@Address];"-";[@UserName]); TRUE; "")

About @ symbol in Excel
https://support.microsoft.com/en-us/office/implicit-intersection-operator-ce3be07b-0101-4450-a24e-c1c999be2b34

[RoYoMi]

Thank you for having a look.

The $ExcelFile.Workbook.FullCalcOnLoad is set to true and $ExcelFile.Workbook.CalcMode is set to automatic.

Following your advice, I reduced the formula and found the injected @ symbol at character 84 was not causing a problem per se. I was mislead there because removing the extra character committing the formula change was two changes resulting in a working formula.

This problem is effecting all formulas. When the file is opened in Excel all formula cells read #NAME?. Clicking into the edit mode on each formula cell, making no changes, then pressing the enter button causes the formula to just start working as expected.

Using the Close-ExcelPackage $ExcelFile -Calculate, doesn't solve the problem either. Likely because excel isn't installed on the computer where I'm processing data this so the recalculate can't happen.

It's odd that when the file is opened in Excel that Excel leaves the formulae un-calculated until they're individually selected and committed. Even triggering Formulas --> Calculate Now from inside Excel doesn't kick them into working mode either :(

[dfinke]

@RoYoMi ah, too bad. Cool you found the -Calculate that popped into my head. Yeah the approach in ImportExcel is all file based and there are things that only get populated if you open it. For example, if you use -Autosize, when saved it is best guess by the underlying DLL. So, not all columns are wide enough when opened.

One more step if you want to go deeper. Create the xlsx, copy it to -original, then unzip it and inspect the XML that gets generated. Then open the file, click on the cell so Excel handles it, save it then unzip it and compare the XML. Then you should have a before and after and some additional clues.

[RoYoMi]

Ok I found a work around. It's not ideal because it does require having Excel installed, but the result is a clean file with working formulae and tables, all without having to manually edit the file.

I packaged this as a script method attached to a file object because it fits with my patterns but this could be written as a function if needed.

Example use:

$File = [System.IO.FileInfo]'C:\PathToFile\TestExcel.xlsx'
$File.Excel_ReapplyFormulae_AllCells( 'TabName' )

Here is the script method incase someone else encounters the same problem:

Update-TypeData -Force -MemberType ScriptMethod -TypeName System.IO.FileInfo -MemberName Excel_ReapplyFormulae_AllCells -Value {
    <#
        .SYNOPSIS
            Correct formulae assigned to cells so they work with tables and fix the #Name?

        .DESCRIPTION
            After creating a worksheet with a table containing formulae, some formulae will work while others may report #Name? results.
            Additionally if the user inserts a new row into the table, Excel may not automatically fill the default formulae down into the new row.

            To correct this the user needs to manually edit each cell containing a formula, make no change, and press enter.

            This script method automates the process, by using a COM connection to Excel to: 
                - open the file
                - find the worksheets
                - find all cells containing a formula
                - reassigning the formula to the cell
                - triggering calculate on the cell
                - save the Workbook

        .PARAMETER WorksheetName
            String or Array of Strings; Optional
            If provided then these sheetnames will be processed.
            If not provided then all sheets in the file will be processed.

        .EXAMPLE
            If you have a specific sheet name 

                $File = [System.IO.FileInfo]'C:\PathToFile\TestExcel.xlsx'
                $File.Excel_ReapplyFormulae_AllCells( 'TabName' )
    
        .EXAMPLE
            To update all sheets

                $File = [System.IO.FileInfo]'C:\PathToFile\TestExcel.xlsx'
                $File.Excel_ReapplyFormulae_AllCells()
    
    #>
    param (
        [string[]]$WorksheetNames 
        ) # end param

    if ( -not ( Test-Path $This ) ) {
        throw ":0017 File '$( $This.FullName )' does not exist"
        } # end if 

    try {
        #region Open the file
            $Excel = New-Object -ComObject Excel.Application
            if ( -not $Excel ) {
                throw ':0063 COM connection to Excel not created'
                } # end if 

            "Opening Excel file '$( $This.FullName )'" | Write-Host
            $Workbook = $Excel.Workbooks.Open( $This.FullName )
            #endregion

        #region Get inscope worksheets
            $WorksheetNames = $WorksheetNames | Where-Object { $( [string]$_ ).Trim() -ne '' }
            $Worksheets = if ( $WorksheetNames ) {
                $Workbook.Sheets[ $WorksheetNames ]
                }
            else {
                # use the flavor that fits your mood, I personally like the all or specific
                # $Workbook.ActiveSheet
                $Workbook.Sheets
                } # end if 

            if ( -not $Worksheets ) {
                throw ':0082 Worksheet not found'
                } # end if 
            #endregion

        foreach ( $Worksheet in $Worksheets ) {
            #region Define the range of cells on the current worksheet
                $UsedRange = $Worksheet.UsedRange
                $RowCount = $UsedRange.Rows.Count
                $ColumnCount = $UsedRange.Columns.Count
                #endregion

            "Procesing worksheet '$( $Worksheet.Name )' '$( $RowCount )' rows and '$( $ColumnCount )' columns" | Write-Host

            #region Reapply all formulae to each cell with a formula
                for ( $RowIndex = 1; $RowIndex -le $RowCount; $RowIndex ++ ) {
                    for ( $ColumnIndex = 1; $ColumnIndex -le $ColumnCount; $ColumnIndex ++ ) {
                        $Cell = $UsedRange.Cells( $RowIndex, $ColumnIndex )
                        if ( $Cell.HasFormula ) {
                            "Worksheet '$( $Worksheet.Name )' Cell '$( $ColumnIndex.ToBase( 'Excel' ) )$( $RowIndex )' HasFormula '$( $Cell.HasFormula )' = '$( $Cell.Formula )'" | Write-Host
                            $Cell.Formula = $Cell.Formula
                            [void]$Cell.Calculate()
                            } # end if

                        } # next ColumnIndex
                    } # next RowIndex
                #endregion

            $Excel.Calculate()
            } # next Worksheet
        
        #region Save and Close the file
            "Closing Excel file '$( $This.FullName )'" | Write-Host
            $Workbook.Save()
            $Workbook.Close()
            #endregion

        #region Garbage collection
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject( $Worksheet )    | Out-Null 
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject( $Workbook )     | Out-Null 
            #endregion
        } # end try
    finally {
        $Excel.Quit()

        #region Garbage collection
            [System.Runtime.Interopservices.Marshal]::ReleaseComObject( $Excel )        | Out-Null 

            [System.GC]::Collect()
            [System.GC]::WaitForPendingFinalizers()
            #endregion
        } # end finally
        
    } # end scriptblock Excel_ReapplyFormulae_AllCells

[dfinke]

Good there is a workaround. Unfortunately, -ComObject is needed.

Did you go down the rabbit hole of unzipping and comparing the XML? Some have done that, iirc, for tweaking pivot tables etc.
Updating the XML directly.

Just a potential option.

[RoYoMi]

Not yet, I'll eventually head that way to eliminate this workaround, but I needed a quick solution and went the dirty route.