Reading multiple sheets from Excel in SAS

The code below let’s you import the sheets found in an Excel-file to SAS datasets.

/*
ExcelFile: The Excell-file containing the sheets that you want to import.
Prefix: Prefix of the datasets imported into work.
*/

%macro ReadExcelSheets(ExcelFile=, Prefix=, OutLib=work);

     libname XLData "&ExcelFile." access=readonly;

     %let Delimitor = ¤;

     proc sql noprint;
       select compress(memname,"$") into :Sheets separated by '&Delimitor.'
             from dictionary.tables
             where libname = "XLDATA" and upcase(memname) like '%$'
       ;
     quit;

     %put Sheets          : &Sheets.;
     %let NumberOfSheets = %sysfunc(countw(&Sheets., &Delimitor.));
     %put Number of sheets: &NumberOfSheets.;

     %do J=1 %to &NumberOfSheets.;
         %let Sheet = %scan(&Sheets., &J, &Delimitor.);
         %put Processing: &Sheet.;

         %if %length(&Prefix) %then %let Prefix = &Prefix._;
         proc import out=&OutLib..&Prefix.&Sheet. datafile="&ExcelFile."
                dbms=xls replace;
                sheet="&Sheet.";
                getnames=yes;
         run;

     %end;

     libname XLData clear;
%mend ReadExcelSheets;

%ReadExcelSheets(ExcelFile=c:\temp\excelfile.xls, Prefix=test);

 

Opening PowerPivot data with Excel in Sharepoint 2010

It’s is not possible to use Excel through Sharepoint 2010 on PowerPivot data, if you are using a claims based authentication provider.

Although classic mode sign-in is not required for the more common data access scenario (where PowerPivot data is extracted from the same Excel workbook that renders it) do not attempt to use PowerPivot for SharePoint with SharePoint web applications that are configured to use other authentication providers. Doing so will result in a connection failure whenever users try to connect to PowerPivot workbooks as an external data source.
Source: http://msdn.microsoft.com/en-us/library/ee210621.aspx

If anyone has a solution to this please let me know by commenting this post.
One possible solution could be to use a third party OLAP-browser for example the freeware version of Ranet Uilibrary Olap or the commercial version of Ranet Uilibrary Olap.