topic Re: Import protected excel file into sas in SAS Programming

Import protected excel file into sas

deleted_user — Fri, 23 Apr 2010 10:51:25 GMT

Hi,
I need to import a password protected excel file into sas. I've checked in the forum and i've found someone who suggested to use FILENAME and DDE engine, becouse DDE acceps parameter like a password, but i don't know how to do it.

X '"C:\Documents and Settings\A.xls"';
filename datafile dde 'Excel|C:\Documents and Settings\[A.xls]sheet1!r2c1:r6c3';
data tmp;
infile datafile missover;
input A B C;
run;

Can anybody help me?
I've looked a solution everywhere but i haven't found anything, so if it's not possible to import a protected excel file to sas, please, let me know and at least i will try another solution.

Thanks!
Elena

Re: Import protected excel file into sas

Oleg_L — Fri, 23 Apr 2010 14:30:49 GMT

Hello!

To read password protected Excel file using DDE:

1. start Excel.

2. open file using open command (where abcdxxx is a password 😞

filename cc dde 'Excel|System';
data _null_;
file cc; put '[Open("c:\documents\wg.xls",0,1,,"abcdxxx")]';
run;

3. Read data from opened file (where SA_Format is a name of Excel sheet):

filename bs dde "excel|SA_FORMAT!r1c1:r1000c4";
data balconst.sarp;
infile bs ls=5000 missover notab dlm='09'x dsd;
informat totlevel 5. acc $8. accnr $200. accnr2 $200.;
input totlevel acc accnr accnr2 ;
run;

Regards,
Oleg.

Re: Import protected excel file into sas

deleted_user — Fri, 23 Apr 2010 15:04:04 GMT

Thanks!!!!