The SAS Output Delivery System and reporting techniques

Problems with updating Excel sheet

Reply
Occasional Contributor
Posts: 6

Problems with updating Excel sheet

Hi,

I'm having trouble to update an existing sheet in an Excel workbook. I'm using the following code:

libname xls excel "file.xlsx" scan_text=no;

proc datasets lib = xls nolist;

     delete 'ValueLevel$'n 'WhereClauses$'n;

run;

quit;

data xls.'ValueLevel$'N(dblabel=yes);

   set to_vallev;

run;

data xls.'WhereClauses$'N(dblabel=yes);

   set to_whecla;

run;

libname xls clear;

When using this code, the following error occurs:

ERROR: The MS Excel table ValueLevel$ 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.

ERROR: The MS Excel table WhereClauses$ 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.

Is there anyone who has an idea on how to update the already existing Excel sheet?

Regards,

Tommy

Super User
Super User
Posts: 7,976

Re: Problems with updating Excel sheet

Posted in reply to tommyschyman

Hi,

Yes, my suggestion would be to export your data to CSV, then in your Excel file some write a VBA script to open the exported data and update your existing WB.  The simple fact is that whilst SAS supports writing out to Excel files via various methods, those methods are output prodedures.  I.e. they are meant to produce a report.  Excel is not a database (or anything else for that matter), so to force it to do things which you would expect in software built to do the task, you need to force it, or use the tools within itself. 

Super User
Posts: 19,822

Re: Problems with updating Excel sheet

Posted in reply to tommyschyman

Hmm...that's usually the method I use, first drop the table and then recreate it and it works. The only difference is that I use proc sql to drop the tables instead of proc datasets. Is your proc datasets working and is your excel file closed?

Occasional Contributor
Posts: 6

Re: Problems with updating Excel sheet

The excel file is closed and the proc datasets seems to work:

557   proc datasets lib = xls nolist;

558        delete 'ValueLevel$'n 'ValueLevel$_xlnm#_FilterDatabase'n 'WhereClauses$'n;

559   run;

NOTE: Deleting XLS.'ValueLevel$'n (memtype=DATA).

NOTE: Deleting XLS.'ValueLevel$_xlnm#_FilterDatabase'n (memtype=DATA).

NOTE: Deleting XLS.'WhereClauses$'n (memtype=DATA).

560   quit;

NOTE: PROCEDURE DATASETS used (Total process time):

      real time           0.01 seconds

      cpu time            0.03 seconds

Have tried to use proc sql but get the same result. When opening the excel file after libname clear the deleted sheets are empty on data. I can't figure out why it's not working.

Super User
Posts: 19,822

Re: Problems with updating Excel sheet

Posted in reply to tommyschyman

Sometimes you have a named range and a sheet with the same name, what does your library look like when you navigate to it in the SAS explorer window?

Occasional Contributor
Posts: 6

Re: Problems with updating Excel sheet

There are no ranges in the excel sheet, so in the SAS explorer there are only the sheet names with the $ as suffix. The actual excel sheets are using the filter function, and that is not deleted when deleting the sheets using proc datasets or proc sql. Can that be an issue?

Super User
Posts: 19,822

Re: Problems with updating Excel sheet

Posted in reply to tommyschyman

Try dropping both and see if it works.

Occasional Contributor
Posts: 6

Re: Problems with updating Excel sheet

I removed the filter function in the actual Excel sheets, but it will not work... As I said there are no ranges, so the only thing that can be deleted are the two sheets I want to update. There is no problem to create a new sheet in the Excel workbook, so there must be something in the actual sheets that blocks this update.

Super User
Super User
Posts: 7,976

Re: Problems with updating Excel sheet

Posted in reply to tommyschyman

It could be your hitting one of those "features" of Excel.  Could be hidden items, formatting, special characters.  It is the problem with using Excel I am afraid, its not in any sense structured or controlled, so your problem could be quite an array of issues.  Try highlighting the cells on one sheet in Excel, and then on the Home tab select the drop down arrow next to Clear.   Select each item in turn and see if anything changes on the sheet.  Also check you have no special characters anywhere.  Also check that all the items on the sheet are readable by SAS, ie, look at the dataset in the libname you create.  Any hidden rows/columns etc.  Or save yourself a headache and stop using Excel Smiley Happy

Ask a Question
Discussion stats
  • 8 replies
  • 1159 views
  • 0 likes
  • 3 in conversation