The SAS Output Delivery System and reporting techniques

Formulas do not update in Excel after updating an existing range.

Reply
New Contributor
Posts: 3

Formulas do not update in Excel after updating an existing range.

Suppose I have a formula referencing a cell in a named range (the formula is NOT in the named range);

If I update this range with either PROC EXPORT or the Libname engine, the formula will not have been updated upon opening the workbook.

 

The following code will successfully replace the data in NAMEDRANGE with the contents of Mydata.

 

FILENAME XLOUT "path-to-workbook\XLOUT.xlsx";
PROC EXPORT DATA=Mydata DBMS=EXCEL OUTFILE=XLOUT REPLACE; 
SHEET="NAMEDRANGE";
RUN;

A formula in another sheet points to a cell in NAMEDRANGE, and I would expect it to reflect the changes made in NAMEDRANGE after opening the workbook. Instead, the formula will reference the last "unupdated" value.

 

The formula will not update from the Calculate buttons either. The only way to "jumpstart" the formula is to either manually enter the formula cell or to use Application.CalculateFull in VBA. Neither of these methods are viable solutions if you want a reliable way to do reporting with .xlsx.

 

I suspect that the formula is failling to update because PROC EXPORT and the libname engine do not actually invoke Excel, they modify the .xml files directly, and they fail to set a "in-need-of-recalculation" flag in the affected cells. I think this flag is used by the Calculate buttons in Excel, thus why they aren't working.

 

Is there a way to fix this preferably from the SAS session ?

 

 

Post a Question
Discussion Stats
  • 0 replies
  • 329 views
  • 1 like
  • 1 in conversation