DATA Step, Macro, Functions and more

Replace and Export Tab in Excel (2013) using SAS

Reply
Contributor
Posts: 27

Replace and Export Tab in Excel (2013) using SAS

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 

Super User
Posts: 9,687

Re: Replace and Export Tab in Excel (2013) using SAS

[ Edited ]
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;

Super User
Posts: 3,112

Re: Replace and Export Tab in Excel (2013) using SAS

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;
Contributor
Posts: 27

Re: Replace and Export Tab in Excel (2013) using SAS

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.

 

Ask a Question
Discussion stats
  • 3 replies
  • 178 views
  • 1 like
  • 3 in conversation