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

Hi,

 

I found the following macro to open an already existing Excel file:

 

%MACRO Sleep(pSeconds);
DATA _NULL_;
vVar1=SLEEP(&pSeconds);
RUN;
%MEND;

 

/* Macro to open Excel and an Excel file */
/* Parameters:
pFile: file name with path and without extension */
%MACRO OpenXls(pFile);
OPTIONS NOXWAIT NOXSYNC MISSING='';
X "C:\Archiv~1\micros~2\Office10\excel.exe / &pFile..xls";
%sleep(2);
%MEND;

 

In my case pFile = C:\yearly_rate  (and the extension is xls). 

But when I ran the code the prompt command opens and closes instantly and the file doesn't open. I tried putting the extension of my Excel:

C:\Documents and Settings\All Users\Start Menu\Programs\Microsoft Office

but still don't get a result...

 

Could you please help me find the error in my code?

 

Thank you!

 

 

ps: I use SAS 9.3

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you have spaces in your file names then you need to add quotes around them or else the command shell will think the spaces mark the end of the command or file name.  The X command will use the outer quotes to find string to send as the command so you need to add more quotes around the command and/or filename.

You could use single quotes to quote the command that you are gving to the X command.

X '"C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"'; 

If you want to use double quotes around the outside then you need to double the inside ones.

X """C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"""; 

You can use the QUOTE() function to make it easier.

X %sysfunc(quote("C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls")); 

 

View solution in original post

12 REPLIES 12
LinusH
Tourmaline | Level 20
Not sure if I can help you with the specific problem.
But what are you trying to achieve and why?
Data never sleeps
ilikesas
Barite | Level 11

Hi,

 

I actually found a paper about SAS macros : http://www2.sas.com/proceedings/forum2007/059-2007.pdf

 

Some of the macros seem interesting to me and I could use them, but as very often happens I encountered problems at the very first step...

 

Thanks!

Reeza
Super User

Your path to the Excel application seems incorrect. The Excel application is usually found under C:\program files\ not your user path. 

 

You can turn on the xwait/xsync commands to see the command line interface and if it generates an error there. 

 

Why are you trying to use DDE and an Xls file? Xls files are pretty outdated ... At least 6 years old now. 

 

 

ilikesas
Barite | Level 11

Hi,

 

I ctually found a paper with SAS macros for Excel and some of them can be very helpful for me:

http://www2.sas.com/proceedings/forum2007/059-2007.pdf

 

I think that I found the path to my Excel, but when I try opening my file it still doesn't open. Even just opening Excel doesn't work, here is the code that I use just to try to open Excel: 

%MACRO Sleep(pSeconds);
DATA _NULL_;
vVar1=SLEEP(&pSeconds);
RUN;
%MEND;


/* Macro to open Excel */
%MACRO Open_Xls();
OPTIONS NOXWAIT NOXSYNC MISSING='';
X "C:\Program Files\Microsoft Office\Office14\excel.exe";
%sleep(2);
%MEND;

 

But again, a very fast opening and closing of command prompt.

 

I use Xls becasue I work on several computers and some of them don't have newer versions of Excel...

 

 

Thank you! 

Reeza
Super User

What's the problem you're trying to solve with the code above? 

Dynamic Data Exchange (DDE) is outdated, and generally not recommended. 

 

If you're trying to get data to excel consider using one of the following methods instead:

 

Proc Export - DBMS=XLSX allows for many options, especially in the latest SAS release

ODS Excel - generates native XLXS files

ODS Tagsets.excelxp

A Poor/Rich SAS Users Proc Export Macro (http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export) 

 

If you explain your problem, you'll get better solutions. 

 

If you really want to continue with DDE, read your code line by line. You'll see the line below:

 

OPTIONS NOXWAIT NOXSYNC MISSING='';

Change it so you remove the NO portion so that you can see the pop up window, which is the DOS/terminal window. 

I recommend reading the documentation so you know what each of those options is doing. Running code you don't understand can be a quick way to making big mistakes. 

 

Additionally, you can just run the command below, in a brand new SAS session, to see if Excel opens.

 

X "C:\Program Files\Microsoft Office\Office14\excel.exe";
ilikesas
Barite | Level 11

Hi,

 

Basically I have many files in a folder, and each file has two columns: a variable y and variable x. What I would like to do is for each file to make a regression and plot the y vs x, and then resend the folder by email.

 

Also, I figured that Excel can be opened like this: X "start excel";

My test file can also be accessed: X "C:\yearly_rate.xls";

 

But when I try to open my real file which is in a folder: X "C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"; I get an error message that 'C:\Documents' is not recognized as an internal or external command, operable program or batch file.

 

Thank you!

Tom
Super User Tom
Super User

If you have spaces in your file names then you need to add quotes around them or else the command shell will think the spaces mark the end of the command or file name.  The X command will use the outer quotes to find string to send as the command so you need to add more quotes around the command and/or filename.

You could use single quotes to quote the command that you are gving to the X command.

X '"C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"'; 

If you want to use double quotes around the outside then you need to double the inside ones.

X """C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls"""; 

You can use the QUOTE() function to make it easier.

X %sysfunc(quote("C:\Documents and Settings\HP_Administrator\Desktop\sas search\excel_file.xls")); 

 

rajenr
Calcite | Level 5

Hi Friends,

 

Please advice to open an existing exel file using SAS in a cloud environment.

 

Thanks..

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Please don't re-open an old thread for a new question.  Start a new thread, ensure you post what version/type of SAS you are using, what Office you have availabel etc.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I would agree with @Reeza here.  DDE is not a good approach, in some circumstances it doesn't work at all.  If its simply importing a file, use proc import.  However, why do you have many Excel files, with a tiny bit of data in each?  It sounds to me like the first thing which has gone wrong in your problem here is the data transfer.  Where does the data come from, i.e. why can they not put it in an appropriate format (not just why are they using Excel which is not good to start with, but why the many files, why not one file with an additional column).  It just looks like whoereve supplied the file has gone out their way to make the data hard to access.

Reeza
Super User

You do not need to open Excel.

 

You do need to import the data from all of the files, run your regression, obtain your output and send that by email. 

 

1. PROC IMPORT

2. PROC REG

3. PROC SGPLOT

 

If you need to do it in Excel, then I suggest writing a VB macro rather than SAS because that's pretty much what you'd be doing anyways, except with DDE you're stuck using VB 4.0 code rather than the modern day VB code available.

 

 

ilikesas
Barite | Level 11

Hi,

 

Strictly speaking, by adding additional quotes ' ' I managed to open my Excel file with command prompt, so this would answer my question the way that I formulated it in the description.

 

But in reality it is much wiser to get the data into SAS and then work on it, instead of working in Excel and use SAS as an intermediary.

 

Great thanks to all !!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 12 replies
  • 13855 views
  • 10 likes
  • 6 in conversation