SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Replacing a sheet in an excel workbook

Accepted Solution Solved
Reply
Regular Contributor
Posts: 179
Accepted Solution

Replacing a sheet in an excel workbook


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


Accepted Solutions
Solution
‎10-02-2013 02:59 PM
Regular Contributor
Posts: 179

Re: Replacing a sheet in an excel workbook

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

View solution in original post


All Replies
Solution
‎10-02-2013 02:59 PM
Regular Contributor
Posts: 179

Re: Replacing a sheet in an excel workbook

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 2697 views
  • 0 likes
  • 1 in conversation