Date:         Thu, 26 Sep 2002 10:01:45 -0400
Reply-To:     simon.pickles@BARCLAYS.CO.UK
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         simon.pickles@BARCLAYS.CO.UK
Subject:      Re: An easy way to check whether a given sheet exists on an Excel
              workbook
Comments: To: Bart Heinsius <Bart.Heinsius@EOMDATA.NL>

Bart

You could always be a real nerdy and analyse the excel file in binary
format. This code appears to work for Excel 2000 files. The data step
ouputs a list of sheets present by looking for '85'x which seems to start
each sheet name and '8C'x which seems to end the list. Excel 5 files seem a
bit different in that the name start 11 bytes (not 12) after the '85'x and
the list ends in '0A'x not '8C'x. It would take some work to make it robust
but it is a potential solution.

Simon

%let xlinfile=C:\temp\test.xls;

filename INXL2 "&xlinfile" recfm=N;

data xlin;
infile inxl2;
length sheetname $32;
endsheetname=0;
do i=1 to 1000000;
input +0 byte $1.;
 if byte='85'x then do;
  input +11 sheetname $1.;
  do until(endsheetname);
    input +0 sheetchar $1.;
 if sheetchar not in('8C'x,'85'x) then sheetname=trim(left
(sheetname)) !! sheetchar;
    else do;
      output;
   sheetname='';
   if sheetchar='8C'x then endsheetname=1;
   else input +10 sheetchar $1.;
 end;
  end;
 end;
end;
stop;
run;