Exporting to Excel via Libname XLSX

[theponcer]

I am trying to export SAS tables to an existing Excel template. The template includes two sheets - "Summarize", and "Test". I want to export the SAS data tables to existing sheets, or named ranges in the template. Here's some sample code:

%let reporting_template_file = \\XXX...\Test Report.xlsx;

data test;
   infile datalines delimiter=','; 
   length item $31. price 8.;
   input item $ price 8.;
   datalines;                      
Blueberries, 1
Strawberries, 2
Huckleberries, 3
Raspberries, 4
Blackberries, 5
Blueberries, 3
Strawberries, 1
Huckleberries, 4
Raspberries, 5
Blackberries, 2

;

libname xl "&reporting_template_file.";

data xl.test;
set test;
run;


libname xl clear;

However, this approach creates a new sheet called "Test1" instead of writing to the existing "Test" sheet. This means that the template will not work as intended, because the "Summarize" sheet pulls off of the data that is supposed to exist in the "Test" sheet.

I understand that there are other solutions to this problem that do not depend on Excel(i.e. proc report). For this task, I am not using proc report. Is there any way to output my SAS tables to an existing sheet in Excel?

I have also tried outputting to xl."test$"n; this should specify that I want to output to the sheet "test". However, this returns the following error: 

ERROR: The MS Excel table test$ has been opened for OUTPUT. This table already exists, or there is a name conflict with an existing 
       object. This table will not be replaced. This engine does not support the REPLACE option.

Similarly, I tried outputting to a previously created named range, xl.test_named_range; this should specify that I want to output to the named range "test_named_range". However, this returns the same error:

ERROR: The MS Excel table test_named_range has been opened for OUTPUT. This table already exists, or there is a name conflict with 
       an existing object. This table will not be replaced. This engine does not support the REPLACE option.

I was originally trying to export these tables using proc export. This method gives me the data in the right place but was taking nearly 50 minutes. All of the tables are very, very small - this process takes 15 seconds when using the libname xlsx method, but the data is in the wrong spot. Any ideas are much appreciated! 

[SASKiwi · Accepted solution]

Just delete the sheet Test first before you rewrite the new version:

proc datasets library = xl nolist nowarn;
  delete test;
run;
quit;

[Reeza]

You don't specify the engine in your LIBNAME statement. Certain engines allow some functionality and others do not. So I don't know what it's using if you don't explicitly state it. I know PCFILES will allow you this type of functionality but I don't think XLSX does.