Excel formulas do not update after SAS export with Excel libname engine

1
votes

I am using the excel libname engine in SAS to update a workbook. In this workbook there are formulas that point to the range exported from SAS. After the export, the formulas will not update anymore unless a user enters the cell and hits Enter.
The calculation is definitely set to Automatic, changing it on/off does not update the formulas.
A similar question has been addressed here without any usefull answers: excel formula not updating after sas export.

Here is my example: export_sheet is the worksheet I export to, it contains the named range rng_export_sheet, formula_sheet contains formulas pointing to export_sheet. sas_table contains the information to be exported:

Libname XLWBK EXCEL "C:\Workbook.xlsm" SCAN_TEXT=NO;

*delete all information in the target worksheet;
proc datasets lib = XLWBK nolist; 
    delete "export_sheet$"n;     
quit;

*clear the named range so that the engine can overwrite;
proc datasets lib = XLWBK nolist; 
    delete rng_export_sheet;     
quit;

*export the data;
DATA XLWBK.rng_export_sheet;
    SET sas_table;
RUN; 

Libname XLOUT CLEAR;

I tried launching a macro in the workbook after the export using .Calculate on each cell containing a formula but it did not activate the formulas.

1
excel-formulasasexport
It seems the only way to programmatically solve this is by using Application.CalculateFull as mentioned here. For the time being I will include this in all my workbooks that are updated this way. – Pane
Sounds like the answer to me! If you self-answer, I'll close the other one in favor of this question as duplicate. – Joe

1 Answers

0
votes

Launching Application.CalculateFull in the target workbook seems to correct the issue, as mentioned here.