DDE ERROR: Physical file does not exist when reading from SAS dataset into Excel Template

[chiangjx]

Hello,

I'm running into an error with using DDE to read from a SAS dataset into a structured Excel Template. I have run this same type of code with another program with no error. What is strange is when I run the x %bquote section, the template file opens in Excel so I know this part is working (and it does the same thing in the working program), but for some reason when it gets to the write in data step, it doesn't seem to notice the excel file exists. The working program even has a more complex template name and structure but it reads everything fine.

I've tried using direct link instead of the &templateName (writing the whole templatepath\templatename out) section also to no avail. Everything else in the program runs fine except for the DDE into Excel portion.

Code:
%let templatePath=\\server\Project_Library\folder1\folder2;
%let templateName=Template.xls;

X %bquote("&templatePath\&templateName"); 

data _null_;
x=sleep(10);
run;

Filename Title DDE "Excel|[&templateName]RPT!r1c3:r1c3";
Filename Title2 DDE "Excel|[&templateName]RPT!r3c3:r3c5";

Data _Null_;
File Title NoTab;
Run;

Data _Null_;
File Title2 NoTab;
Put &date1 "09"x "09"x "09"x;
Run;

The Error code from log:

1135
1136 /* DDE into template */
1137 X %bquote("&templatePath\&templateName")
1137! ;

1138
1139 data _null_;
1140 x=sleep(10);
1141 run;

NOTE: DATA statement used (Total process time):
real time 10.04 seconds
cpu time 0.03 seconds

1142
1143 Filename Title DDE "Excel|[&templateName]RPT!r1c3:r1c3";
1144 Filename Title2 DDE "Excel|[&templateName]RPT!r3c3:r3c5";

1178
1179 /*Populate Title*/
1180 Data _Null_;
1181 File Title NoTab;
1182 /* Put &date "09"x; */
1183 Run;

ERROR: Physical file does not exist, Excel|[Template.xls]RPT!r1c3:r1c3.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

1184
1185 Data _Null_;
1186 File Title2 NoTab;
1187 Put &date1 "09"x "09"x "09"x;
1188 Run;

ERROR: Physical file does not exist, Excel|[Template.xls]RPT!r3c3:r3c5.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

At this point I'm at a loss. Am I missing something? Much appreciated for the help! 

[SASKiwi]

Where are you opening Excel? Your code will only work if you have an Excel session open, either started manually or started in your code. I see you have a DATA step sleeping for 10 seconds. Maybe you missed out starting Excel in that step.

[chiangjx]

Hi,

this code opens excel for me:
X %bquote("&templatePath\&templateName");

[VDD · Accepted solution]

here is a sample of what I use:

options noxwait noxsync mlogic;
X "C:\Program Files\Microsoft Office\Office\EXCEL.EXE";
data _null_;
x=sleep(5);
run;
filename cmds dde 'excel|system';
data _null_;
length mystuff $250;
file cmds;
pre_book = '[open("'||"&temppath.\&workbook."||'")]';
put pre_book;
run;
filename outpre dde
"Excel|&temppath.\[&workbook.]&sheet.!r1c1:r561c10" notab;

/* put the information into the cells in the worksheet */
data _null_;
file outpre notab;

/* a lot of puts and x'09' */

run;

[chiangjx]

Thanks! Your code helped me remember that Excel Sheet name was a thing. Changed it in the code to match excel and it works now. Have a good weekend!