BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

Hello,

 

I would like to execute an Excel VBA using SAS EG 8.3.  Excel is hosted on my c drive while the Excel file itself is hosted on a window server.  SAS .

 

Here's my sript , follow by the log file.

options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" ';
/* Sleep for 5 seconds to give Excel time to come up */
data _null_;
x=sleep(5);
run;
filename cmds dde "excel|system";

data _null_;
file cmds;
/* Open the excel file test.xlsm which contains the VBA macro */
put '[open("P:\Temp\TestStephane.xlsm")]';
/* Run myVBAmacro */
put '[run("TestStephane.xlsm!genereGus")]';
run;

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
28
29 options noxwait noxsync;
_______
13
ERROR 13-12: Unrecognized SAS option name NOXWAIT.

29 ! options noxwait noxsync;
_______
13
ERROR 13-12: Unrecognized SAS option name NOXSYNC.

30 x '"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE" '
30 ! ;
31 /* Sleep for 5 seconds to give Excel time to come up */
32 data _null_;
33 x=sleep(5);
34 run;

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

35 filename cmds dde "excel|system";
ERROR: Invalid device type.
ERROR: Error in the FILENAME statement.
36
37 data _null_;
38 file cmds;
39 /* Open the excel file test.xlsm which contains the VBA macro */
2 The SAS System 09:11 Thursday, April 20, 2023

40 put '[open("P:\Temp\TestStephane.xlsm")]';
41 /* Run myVBAmacro */
42 put '[run("TestStephane.xlsm!genereGus")]';
43 run;

ERROR: Insufficient authorization to access /opt/sas94/Config/Lev1/SASApp/cmds.dat.
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
***********

 

One more question.  How do you close the excel file ? Does this command will work ?

put '[close("P:\Temp\TestStephane.xlsm")]';

 

1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Onyx | Level 15

you are trying to to run local excel 

"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE

on remote SAS server:

/opt/sas94/Config/Lev1/SASApp/

 

no way it will happen...

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

3 REPLIES 3
yabwon
Onyx | Level 15

you are trying to to run local excel 

"C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE

on remote SAS server:

/opt/sas94/Config/Lev1/SASApp/

 

no way it will happen...

 

Bart

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



AlanC
Barite | Level 11
First of all, Bart is correct. Secondly, executing VBA on a server (even if the file is moved) is going to be very, very problematic. Not worth it. Hence, I suggest abandoning your VBA plans.

So, what are the alternatives? VBA should be reserved for interactive work, not file work. VBA is not recommended, regardless, and professional shops use VSTO for anything serious.

If the goal is to change something in the file, use a file manipulation technique. I use C# and GemBox but you can also use Excel interop. Python has something similar.

DDE is mis-spelled. It should be DEAD 😉 Seriously, dont ever use it.
https://github.com/savian-net
Sajid01
Meteorite | Level 14

Hello @alepage 
In order to run excel vba using SAS EG is that SAS Should be installed on a Windows machine and excel should be installed on the same machine  and users have the necessary permissions.
From the log it appears that SAS is running on a Linux/Unix server. So what you want is not a feasible preposition.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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