Replacing a sheet in an excel workbook

[BenConner]

Hi,

Looking through Art Carpenter's Innovative SAS Techniques, on page 7 he shows a technique for updating a sheet in an excel workbook.  In this example he used an Office 97/2003 type workbook (.xls).

Tried to duplicate this with a current style workbook in Windows SAS 9.2 and got an error.

1. I created a test workbook (test.xlsx) with a single sheet named 'test'.
2. I then used the following libname to get to it:
   libname testx excel '...path to sheet\test.xlsx' scan_text=no;
3. Ran a proc datasets to delete the file (had to use a $ after the name--it couldn't find the sheet name just as 'test'):
   proc datasets library=testx nolist;
     delete 'test$'n;
     quit;
4. Then used a simple data step to try to replace it:
   data testx.'test$'n;
   set temp;
   run;

The error I got back was:

ERROR: The MS Excel table test$ has been opened for OUTPUT.  This table already

         exists, or there is a name conflict with an existing object.  This table

         will not be replaced.  This engine does not support the REPLACE option.

NOTE: The SAS System stopped processing this step because of errors.

Suggestions?

Thanks much!

--Ben

[BenConner · Accepted solution]

Found the answer to the question I posed; apparently the technique illustrated is very specific to .xls formatted Excel files.  I ended up using a Proc Export with a Replace option, but had to name the tab and the columns on that tab with a named range identical to the tab name.  Example:

Proc Export data=work.scores

   outfile='c:\temp\classes.xlsx'

   dbms=EXCEL

   Replace;

  sheet='classscores';

run;

The tab classscores had a named range of classscores defined on it as well.  SAS was able to delete and replace the contents nicely.

Many thanks to Sandy O in SAS' tech support for pointing me in the right direction.

--Ben