Your SAS programs, embedded in web apps and elsewhere

DDE from a stored process, error

Reply
Contributor
Posts: 53

DDE from a stored process, error

Hi run the following code from Enterprise Guide to add a custom worksheet to an existing workbook. In Enterprise Guide-->File--->New-->Program, paste the code below in and run, it works fine. Excel opens, tab is added and populated, then the workbook is saved and closed. If I take this exact code and create a stored process out of it I get the error: ERROR: Physical file does not exist, excel|system. Any ideas?



/** initiate the link to Excel **/
options noxsync noxwait xmin;
filename sas2xl dde 'excel|system';

data _null_;
length fid rc start stop time 8;
fid=fopen('sas2xl','s');
if (fid le 0) then do;
rc=system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid=fopen('sas2xl','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
rc=fclose(fid);
run;

/** lets get manipulating! **/
data _null_;
file sas2xl;

/** open the workbook */
put '[error(false)]';
put '[open("c:\temp\web_output.xls")]';

/** create a new tab for the summary **/
put '[workbook.insert(1)]';

/** name the newly created sheet **/
put '[workbook.name("sheet3","Summary")]';
run;
SAS Super FREQ
Posts: 8,739

Re: DDE from a stored process, error

Hi:
I can think of a few things:
1) Remember that a stored process is running on either the Stored Process Server or the Workspace server. If the server is a UNIX or LINUX server, it probably does NOT have a C drive. So your physical file error could come from your references to c:\temp. If your server is a Windows box and it DOES have a C drive, then there might not be a TEMP directory on that drive.
OR
2) The server on which the stored process is running does NOT have Excel (which would also be the case if the server was a UNIX or LINUX server). This would make your "start excel" command have problems.
OR
3) Also remember that the Stored Process Server and the Workspace Server generally do not have write access to YOUR machine's C drive (if you wanted to have the XLS file returned to your C drive).

Are you using the SAS Add-in for Microsoft Office?? When you submit an SP using the Add-in, you have the choice to put the SP results into a new workbook or a new sheet in the existing workbook.

Even though your program works in EG (are you submitting this on a LOCAL server or LOCAL installation of SAS?), I'm not entirely sure that it would work as a stored process. This is probably a question for Tech Support. They would know whether this would work or whether there are VB/.NET methods to use to add a sheet to an existing Workbook by calling a stored process.

To open a track with Tech Support, fill out the form at this link:
http://support.sas.com/ctx/supportform/createForm

cynthia
Ask a Question
Discussion stats
  • 1 reply
  • 297 views
  • 0 likes
  • 2 in conversation