I'm running the below code to copy an Excel template, then replace the two tables aHeader and aException in the output file, to which I assign the libname wbkOut. When I run this, I get the following error message from the data step which creates the table aHeader:
ERROR: The MS Excel table aHeader 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.
However, the data step creating the table aException runs fine:
There were 1305 observations read from the data set WORK.EXC_DEF_WO_LINK.
NOTE: The data set WBKOUT.aException has 1305 observations and 9 variables.
data wbkOut.aException (dblabel=yes);
retain region case_current_court case_officer case_number doc_type doc_number doc_filed_due_d
libname wbkOut clear;
Can anyone spot anything in here that could be causing this error? I've tried assigning the libname separately first for replacing aHeader, then clearing and assigning again to replace aException - when I did this the aHeader table was replaced ok and replacing the aException table threw an identical error. Both of these tables can be seen from the Server List in Enterprise Guide when the libname is assigned - after the above code runs wbkOut.aHeader can be seen as the 5 columns F1-F5 with 0 rows.
I'm using SAS Enterprise Guide v4.1 on Windows XP - the SAS version is 9.1 and the Excel file format is Excel 97/2003 (but the file was created using Excel 2007).
Thanks for the reply Linus. I'm using the data step because, as far as I'm aware, I'm unable to issue commands to the OS from Enterprise Guide. Having said that, I'd be delighted to be proved wrong in this - copying a file byte by byte is clunky in my opinion and it would be far easier and more convenient to be able to direct Windows to perform this step itself.
As it happens, I've got it working by changing the name of the range 'aException' to 'bException', and re-ordering the sheets in the workbook - this makes no sense to me but I'll take it. It may well help to clear the filename after the copy (which I've now done), but I suspect the problem may be something to do with interaction between SAS/Access and the JET provider. There are a couple of other defined names in the workbook which refer to formulae rather than to static ranges, and thus require evaluation by Excel before returning any values. Even though the SAS script does not refer to these names at all, in my experience JET has trouble with these sorts of names (i.e. they can interfere with JET reading other names). I'm now considering whether it might be worth redesigning the workbook so that dynamic names are no longer used.