Replace excel tab but not the file

[BETO]

hi I have  a excel  file inside is a pivot table tab  with a data dump which feeds the pivot table

ccurrently I'm using

proc export data = report

outfile= " c:\report.xlsx"

dbms=excel replace;

sheet="data_dump";

run;

it replaces the whole file I need it to just replace the tab so the pivot table can refresh with new data on open thanks again

[Haikuo · Accepted solution]

It maybe engine depended, but for the PCFILES engine, I can't seem to replace the tab directly, rather, I can delete it then make a new one.

libname xl PCFILES path="C:\temp\_test.xls";

data xl.class;

set sashelp.class;

run;

data xl.cars;

set sashelp.cars;

run;

quit;

libname xl clear;

libname xl PCFILES path="C:\temp\_test.xls"  AUTOCOMMIT=YES;

proc datasets library=xl;

delete cars;

quit;

data xl.cars;

set sashelp.class;

run;

libname xl clear;

I have 32-bit MS office products and SAS 64-bit coexisting, so I can't test EXCEL engine.

Haikuo

[BETO]

I Tried something like that and it worked but the tab doesn't exist  it places the data right next to the pivot table.. When the tab is in the file I get and error message of it can't replace because a tab already exist...I think I will delete tab first an then paste

[ChrisHemedinger]

If you can use DBMS=XLSX and you have SAS 9.4, then you can replace an existing sheet without replacing the entire workbook/file.  See an example here:

http://blogs.sas.com/content/sasdummy/2014/09/21/ods-excel-and-proc-export-xlsx/

Chris

[art297]

Can you post an example workbook and an example SAS dataset that you'd like to upload?

[jakarman]

IF your question is why the only excel (xls or zipped xml-s xlsx) is being replaced, well it is only one file on the Windows environment. There are segregated parts.

As there are no segregate parts how would you handle them separately?
This is also the root cause for locking issues. Excel is not a DBMS or really designed for that.