BookmarkSubscribeRSS Feed
craig159753
Quartz | Level 8

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

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

 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
Quartz | Level 8

FALSE or 0 are correct.

 

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

Reeza
Super User

@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
Quartz | Level 8

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
Super User

Which piece of code generated that error?

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

The above bit 

Reeza
Super User

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.  

ballardw
Super User

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

Reeza
Super User

@ballardw

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 5162 views
  • 0 likes
  • 5 in conversation