We have a lot of spreadsheets and there is a need to upgrade an important Addin.
The change will involve converting an old formula to a new one. It is not a straightforward change because the name and number of parameters are not the same.
Therefore we are investigating how to do it automatically. The new addin is only available in new PCs. It is not installed on my workstation.
Let's say the old formula is oldf and the new one newf
My code will do somehting like this:
ws.Cells.Item(cell_r, cell_c).Formula = "=newf(A1, B2, """CCC""")"
Then I will call
workbook.saveAs() to save to a new workbook
Now when I open the new workbook in the new PC, I can see the cell becomes '#NAME'. If I select the cell, do a F2 and then F9, it will work as expected. But it defeats the whole purpose of the automation.
My theory is: when I set the cell to a new formula in my Old PC, since the excel COM object has no idea of the existence of the new addin, so it marks the cell in some invalid internal state. It will not refresh even if it opens in the new PC.
What can be done to fix it?