01-05-2017 10:44 PM
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.
01-05-2017 11:43 PM - edited 01-06-2017 05:37 AM
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;
01-06-2017 01:21 AM
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;
01-06-2017 08:33 AM
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.