topic Query excel to find sheet name in SAS Programming

Query excel to find sheet name

deleted_user — Thu, 08 Jul 2010 08:44:53 GMT

Hi All

I was just wondering if there is a function or DDE query I can run to find the sheet names within Excel? E.G. Book1.xls contains Sheet1 Sheet2 Sheet3.

I have a client that sends me weekly updates but always calls the sheet name something different. I have asked him not to but he continues to change it.

Thanks

Re: Query excel to find sheet name

deleted_user — Thu, 08 Jul 2010 13:30:22 GMT

Try this

libname xls excel 'c:\test.xls';

data t;
set sashelp.vtable;
where libname = 'XLS';
run;

GL

Re: Query excel to find sheet name

Oleg_L — Thu, 08 Jul 2010 14:04:00 GMT

Hi,

Mr. Data _null_ provided very good solution a year ago:

http://support.sas.com/forums/thread.jspa?messageID=23307嬋

Oleg.

Re: Query excel to find sheet name

deleted_user — Thu, 08 Jul 2010 15:38:50 GMT

Thank you both - these are both great ways to do it! Cheers!

Re: Query excel to find sheet name

Peter_C — Thu, 15 Jul 2010 13:56:44 GMT

Neil

just to dig a little into the dictionary tables, you may find the engine column of dictionary.members useful when working with more than one workbook (or libname) .... Try code:[pre] proc sql ;
create table sheets as select * from dictionary.members where engine= 'EXCEL' ;
quit ;[/pre]