topic Re: Open an excel file with SAS in SAS Programming

Open an excel file with SAS

Onizuka — Fri, 17 May 2019 09:48:33 GMT

IHello !

I am trying to open an Excel file with SAS. There is another topic but i don't find the answer on it (https://communities.sas.com/t5/General-SAS-Programming/opening-an-excel-file-with-SAS/td-p/260934).

So there is the code I found on internet :

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

%MACRO OpenXls(pFile);
OPTIONS NOXWAIT NOXSYNC MISSING='';
X '"C:\Program Files\Microsoft Office\Office15\EXCEL.EXE / &pFile..xlsx"';
%sleep(2)
%MEND;

I can open Excel if I delete

 / &pfile..xlsx

But if i don't delete this part and i want to open the specific file, it doesn't work :

My file is on a repertory like this :

L:\CLIENTS\XXX\ETUDES\# PROD\111111 vérité\StatTris\2019T1\DATAS\questionnaire long.xlsx

Any ideas ?

Thank you !

Onizuka

Re: Open an excel file with SAS

Onizuka — Fri, 17 May 2019 12:24:24 GMT

I find a solution which is :

open Excel first
open the file after

%macro Open_FileXlsx(nom_fichier); /* nom_fichier is the path + name + extension of the file */
OPTIONS NOXWAIT NOXSYNC MISSING='';
X '"C:\Program Files\Microsoft Office\Office15\EXCEL.EXE "';
%sleep(1);
FILENAME cmds DDE 'Excel|system';
DATA _NULL_;
FILE cmds;
PUT "[open("'"'"&nom_fichier"'"'")]"; 
RUN;
%mend;

Re: Open an excel file with SAS

ballardw — Fri, 17 May 2019 15:42:43 GMT

DDE is fragile, not being recommended by Microsoft and the communication approach it uses can be conflicted by other programs such that DDE will not work.

So if your approach quits working it is likely to be something external to SAS that changes in your environment.

I found this out when my shop installed Cisco Jabber.

You may also have issues with Office 365 as the Microsoft Office no longer has an "Excel.exe", at least not one that I can find.

Re: Open an excel file with SAS

Onizuka — Mon, 20 May 2019 08:06:47 GMT

@ballardw wrote:
DDE is fragile, not being recommended by Microsoft and the communication approach it uses can be conflicted by other programs such that DDE will not work.

So if your approach quits working it is likely to be something external to SAS that changes in your environment.
I found this out when my shop installed Cisco Jabber.

You may also have issues with Office 365 as the Microsoft Office no longer has an "Excel.exe", at least not one that I can find.

Hello, thank you for your answer, I appreciate it 🙂

For information, i am in an internship until august. I will provide to the company the code with the DDE but telling that it can have some problems. The problem is that the excel file have differents sheets and we don't have the possibility to import "XLS" or "XLSX" or "EXCEL" files on SAS (unless we convert it in CSV but to long because lot of sheets..)

Edit : sorry for my bad english haha