using dde where path contains an apostrophe

[reef]

I have been using dde to send data to an excel file without a problem until I found one case where the file path contains an apostrophe. In this case the dde link fails due to the apostrophe in the path. I tried every masking function to no avail and in the end I had to remove the apostrophe from the folder name. Has anyone resolved a similar case?

Example, 

data d; do x=1 to 10; y=exp(-x); output;end; run;

%let xlf = %bquote(c:\temp\sas\dde'tests\test.xlsm);

filename xl dde "exce|[&xlf.]sheet1!r1c1:r11c2" notab lrecl=200;
data _null_;
file xl;
set d;
put x y;
run;

[ChrisNZ]

Few people will be able to test as most have had to move on from DDE.

Your syntax works fine in SAS when DDE is not involved:

%let xlf = %bquote(c:\temp\tes't.xlsm);

filename XL "&xlf" ;
data _null_ ;
  file XL ;
  put 'a'; 
run;

So I suspect the DDE mechanism gets confused by the apostrophe. Have you tried to double the apostrophe? or to escape it?

Moving on, sadly, DDE will be less and less supported. See http://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/ (from 2014). 

So time to rewrite your DDE logic using another (maybe less practical, but more time-proof) method?

[Kurt_Bremser]

As single (and double) quotes are used in shell scripting, they have no place in file and path names. So I'd rather tackle the problem at its source and remove the special characters than trying an unnecessary and complicated workaround. Keep in mind that those characters will cause problems elsewhere, too.

[reef]

Thanks to all.
I replied as soon as answers were sent but was not aware that my final update was not recorded on the site. 

I have opted to remove the apostrophe from the filename and path.

Best regards and thanks again.