Replacing a sheet in an excel workbook

BenConner — Tue, 01 Oct 2013 17:25:42 GMT

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.

I created a test workbook (test.xlsx) with a single sheet named 'test'.
I then used the following libname to get to it:
libname testx excel '...path to sheet\test.xlsx' scan_text=no;
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;
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

Re: Replacing a sheet in an excel workbook

BenConner — Wed, 02 Oct 2013 18:59:44 GMT

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

topic Re: Replacing a sheet in an excel workbook in Microsoft Integration with SAS

Replacing a sheet in an excel workbook

Re: Replacing a sheet in an excel workbook