topic Re: Error while refreshing Pivot data from sas in SAS Programming

Error while refreshing Pivot data from sas

nitink26 — Wed, 05 May 2021 01:52:43 GMT

Hi,

I already opened excel file but while running below code, I am not able to refresh my pivot sheet -

data _null_;
file Excel;
put '[workbook.select("Pivot")]';
put '[error(false)]';
put '[select("A3B5")]';
put "[pivot.refresh()]";
Put '[save.as("'"&excel_file_out_lan."'")]';
put '[error(false)]';
put '[close.ALL()]';
Put "[quit()]";
run;

error - FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase.

Can someone help me? Thanks!

Re: Error while refreshing Pivot data from sas

nitink26 — Wed, 05 May 2021 01:58:26 GMT

I forgot to mention that I tried available solutions but it didn't work for me. Thanks!

Re: Error while refreshing Pivot data from sas

SASKiwi — Wed, 05 May 2021 05:46:26 GMT

Looks like you are trying to communicate with Excel using DDE which is no longer supported by Microsoft in the most recent versions of Office.

We would need to know what your complete use case is before suggesting alternative approaches.

Re: Error while refreshing Pivot data from sas

ballardw — Wed, 05 May 2021 15:47:20 GMT

If this is a DDE attempt and you have not previously had DDE "working" properly you may have other software interfering with the communication channels DDE uses.

I had one job that was run annually that failed at DDE calls. Found out that CISCO Jabber application loaded since the last run used those channels and caused the failure. There may not be any quick fix as I had to KILL the process not shut down an active program but go into Task Manager and Kill the Jabber process to allow the program to run.

If you are having a similar issue with a different program you may have serious issues with such depending on the nature of the other program, not mention just finding which one(s) may be involved.

Since formal support for DDE has been non-existent for a number of years another approach may be in order.

Re: Error while refreshing Pivot data from sas

nitink26 — Sat, 08 May 2021 09:34:58 GMT

DDE is working fine as I am able to save the Excel workbook but if I try to refresh the pivot I got the error.

is the code correct to refresh the Pivot table sheet? Thanks!