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!
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;
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.
Hi,
this code opens excel for me:
X %bquote("&templatePath\&templateName");
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;
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.