Export to Excel doesn’t refresh internal formulas linked

1

Hello Dmitry

I have an Excel file "MainFile.xlsx" with formulas - example Sheet-MainFile_S1 Field A2 "=Data!A2" -> Works

Now I make a clone of this file to "MainFile-Clone.xlsx"

Now I replace the sheet "Data" with new data from EM

Now I open the file "MainFile-Clone.xlsx" with Excel

  • I see that the data in "Data" is new/replaced

But the formulas are not calculated, there are still the old values ​​in the Sheet-MainFile_S1, and these cannot be recalculated by pressing F9

Excel still has the reference, but does not recognize that the data has been updated and therefore Excel does not recalculate!

I have adopted your recommendation:
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("{Filename}")
$excel.CalculateFullRebuild
$workbook.RefreshAll
$workbook.save()
$workbook.close()
$excel.quit()

How can I get around this error, what is EM's solution - Need more infos in your recommendation?

IMPORTANT: I have to work with .xlsx and not with Macros .xlsm

Regards Reinhard

MainFile_xlsx
MainFile_xlsx813×1021 231 KB

MainFile-Clone_xlsx
MainFile-Clone_xlsx768×1221 391 KB

Data.xlsx (8,5 KB)
Excel-Clone-Sheet-not-Calculate-Formula.morph (7,2 KB)
MainFile.xlsx (10,1 KB)
MainFile-Clone.xlsx (10,5 KB)

2

Hello @Reini,

Try refreshing the "MainFile-Clone.xlsx" file using the "Recalculate workbook" command from the "Excel command" action after the export.

3

Tried all of these things and it doesn't work, it doesn't update!
-> Recalculate calculation F9
-> Calculate sheet Shift+F9
-> Refresh Excel data
-> Workbook calculation is set to Automatic

4

Have you tried the solution I proposed in the previous post?

5

Hello @andrew.rybka

Now I have understood your previous answer correctly.

In "EasyMorph" - "Excel Command" - "Recalculate workbook"
and not the recalculation within Excel.

Yes, it works. Thank you very much for your help

Regards Reini