BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
chiangjx
Fluorite | Level 6

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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;

View solution in original post

4 REPLIES 4
SASKiwi
PROC Star

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
Fluorite | Level 6

Hi,

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

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
Fluorite | Level 6

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!

SAS Innovate 2025: Call for Content

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!

Submit your idea!

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
  • 4 replies
  • 5089 views
  • 0 likes
  • 3 in conversation