SAS DDE: EXCEL file not closing.

[craig159753]

Hi,

I have an issue when using DDE.

What is happening is I make a copy of my EXCEL master file (example.xls) using SAS, I then open this copied EXCEL file using SAS, then simply close it. That's it, howerver the master EXCEL file (i.e. the one I copied) for reason is locked and when I open it, its says "Read Only" at the top until I close my entire EXCEL process. I'm not sure why this is happening. Below is my code.

%let inpath=C:\;
%let infile=example;
%let extension=xls;

%sysexec copy "&inpath.\&infile..&extension."
              "%sysfunc(getoption(work))\&sysuserid._&infile..&extension.";


** Get EXCEL commands;
filename cmdexcel DDE 'EXCEL|SYSTEM';


data _null_;
	** Open EXCEL command;
	fid = fopen('CMDEXCEL', 'S');

	if (fid=0) then do;
		** Open EXCEL;
		rc = system("Start Excel");
		start = datetime();
		stop = start + 10; ** 10sec limit;
	end;
	do while (fid eq 0);
		fid = fopen('CMDEXCEL', 'S');
		time = datetime();
		if time ge stop then fid = -1;
	end;
	if fid lt 0 then put 'ERR' "OR: Excel failed to open (within time limit).";
run;

data _null_;
	** Open file **;
	call system ("'%sysfunc(getoption(work))\&sysuserid._&infile..&extension.', 0, true");
	x = sleep(1);
run;

filename cmdexcel clear;
filename cmdexcel DDE 'EXCEL|SYSTEM';


** Close EXCEL file;
data _null_;
	file cmdexcel;
	x=sleep(3);
	put '[error(false)]';
	put '[CLOSE(false)]';
run;

filename cmdexcel clear;


%sysexec del "%sysfunc(getoption(work))\&sysuserid._&infile..&extension.";

All I need is to ensure the master EXCEL file does not lock and does in fact close (well it technically should never open because I use a copied file). 

Running SAS 9.4 on windows.

Thanks

[RW9]

I am not surprised you are having difficultie with DDE, it is over 20 years old and not supported anymore by MS.  If you really need to use Excel (and that is really your problem there in a nutshell), why not just execute a system copy command to copy the file over.  Or import the file and write it out again after processing.  Or use VBA to load CSV data from SAS, process into your template file and then save to a new file.  There are many options and all of the are better than old unsupported technology.

[craig159753]

I agree, DDE is redundent technology I would rather use PROC SQL but that requires a lisence I do not have.

The copy is done in the %SYSEXEC lines without a problem, my issue is somehow my file is remaining open, when I dont even open it :S 

[RW9]

Errm, proc sql is a querying language, it doesn't create Excel files or anything like that.  By create them I meant use tagsets like excelxp, or the newer libname excel engine.

If the copy is done fine using %sysexec, then why are we taling about DDE?  Please clarify the problem, exactly what you are doing.  As DDE/Excel does not need to be used at all if its just copying a file with the system commands.  It sounds like you have other issues.  

[Reeza]

 I don't think the command is false. 

Try try not using error and a close and quit

Put '[close(0)]';

put '[quit(0)]';

[craig159753]

FALSE or 0 are correct.

QUIT closes all EXCEL process but this means other EXCEL files I have open.

[Reeza]

---

@craig159753 wrote:

FALSE or 0 are correct.

QUIT closes all EXCEL process but this means other EXCEL files I have open.

---

Not necessarily, you can have multiple apps of Excel open at once AFAIK. 

I don't know why you reset your filename reference but it seems unnecessary. 

But you use a different method to open/close your process than I typically use. 

Slide 20 is the code I usually use. As of last week 🙂

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/FareezaKhu...

This is all based on the assumption that it's DDE and not Excel. Another issue with this process is Trist Center settings. So basically perform each step manually and see if you get any errors. 

[craig159753]

Thanks for your comments, I tired you method and got this message 

ERROR: Physical file does not exist, EXCEL|SYSTEM.

However I checked and the file did exists, below is the code.

options noxwait noxsync;
%put %sysfunc(getoption(work));
** Make copy;
%sysexec copy "&inpath.\&infile..&extension."
              "%sysfunc(getoption(work))\&sysuserid..&extension.";

** Open EXCEL;
x "C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE";

** Wait for EXCEL to open;
data _null_;
	x=sleep(3);
run;


** Get EXCEL commands;
filename cmdexcel DDE 'EXCEL|SYSTEM';


** Open EXCEL file;
data _null_;
	file cmdexcel;
	put "[open(""%sysfunc(getoption(work))\&sysuserid..&extension."")]";
	x=sleep(3);
run;


** Clsoe EXCEL process;
data _null_;
	put '[close()]';
	put '[quit()]';
run;


** Delete copy;
%sysexec del "%sysfunc(getoption(work))\&sysuserid..&extension.";

[Reeza]

Which piece of code generated that error?

[craig159753]

** Open EXCEL file;
data _null_;
	file cmdexcel;
	put "[open(""%sysfunc(getoption(work))\&sysuserid..&extension."")]";
	x=sleep(3);
run;

The above bit 

[Reeza]

Run your code with MPRINT and SYMBOLGEN on and post the log from that section. The log should explain why the process can't find that file.  

[rogerjdeangelis]

You might take a look at

https://communities.sas.com/t5/Base-SAS-Programming/SAS-DDE-to-Excel-2013-freezes-SAS/m-p/290384

and

http://goo.gl/klPF29

https://listserv.uga.edu/cgi-bin/wa?A2=ind1608B&L=SAS-L&P=R9492&X=8D8F7E1D023AA7168C&Y=rogerjdeangel...

[ballardw]

I won't claim to be any DDE expert but I'm not sure I see where you are copying the file.

[Reeza]

@ballardw

%sysexec copy "&inpath.\&infile..&extension."
              "%sysfunc(getoption(work))\&sysuserid..&extension.";