Replace and Export Tab in Excel (2013) using SAS

[camfarrell25]

Hello, 

Been trying to replace/delete a sheet in excel (2013 - xslx) and export a dataset to the same workbook, although I seem to be hitting road blocks when it comes to directly modiying the excel spreadsheet. 

I've managed to set the libname: 

libname xls xlsx "(Excel FilePath.xlsx)";

but I'm not able to modify the workbook directly from SAS. 

I've seen some talk about using DDE but if there's a simple solution out there I would appreciate it. 

Any ideas??

Thanks!

CF 

[Ksharp]

Drop it firstly, before export it.


libname xls xlsx "(Excel FilePath.xlsx)";

proc sql;
drop table xls.have;

create table xls.have as 
select * from have;
quit;

[SASKiwi]

In addition to @Ksharp's method, I use PROC DATASETS to delete a sheet then add it again:

proc datasets library = xls;
  delete MySheet;
  run;
  copy in = WORK out = xls;
  select MySheet;
  run;
quit;

[camfarrell25]

When using that code, I get the following error message: 

ERROR: XLS.DATA.DATA cannot be deleted because files cannot be deleted from the XLS library.