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
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;
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;
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.