Updating a microsoft excel workbook

[Reeza]

I have an excel workbook with data and want to update/overwrite the contents on a specific tab.

If I export using DBMS=EXCEL with the replace option, I actually get a second sheet instead of replacing my sheet.

If I export using DBMS=XLS I get a .bak file created, although the file was updated correctly.

This is the error here:http://support.sas.com/kb/37/485.html

I was wondering if anyone had a simple workaround. I remember using this before without this issue.

I'm on SAS 9.2 and trying to update an Excel 2003 file.

[Haikuo]

Have you tried using "libname excel" engine? So you can treat excel sheet like a SAS table.

Haikuo

[Anotherdream]

I will have to agree with Hai.Kuo. If you can open the excel file before inserting the data into it (you can do this with sas itself), then an excel libname looking similar to the following would be the easiest way.

If you go into excel and define a named range on the location that you want your data to be copied into, the following code will delete the existing data from Excel, and then upload the new data from the table MYDATA within the work directory.

libname exl excel "c:\my location\myfile.xlsx";

proc datasets lib=exl;

delete MY_EXCEL_NAMED_RANGE;

run;

data exl.MY_EXCEL_NAMED_RANGE;

set MYDATA;

run;

Hope that helps, and let me know if you have any questions!

[Linlin]

Happy New Year to Haikuo, Reeza and All!

I use libname. then drop the sheet that you want to replace:

libname test "????.xls";

proc sql;

drop table test.the_sheet_you_want_to_replace;

quit;

data test.the_sheet_you_want_to_replace;

    set your_data;

run;

libname test clear;

[Reeza]

So you do need to use literals, 'Sheet_name'n if you try and access without creating a named range, but that still won't let you add into it.

I could possible use an append or something, but I created the named ranges for now. Will keep testing.

155

156  PROC SQL;

157      DROP TABLE TEST.'SOURCE$'n;

NOTE: Table TEST.SOURCE$ has been dropped.

158  QUIT;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.04 seconds

      cpu time            0.03 seconds

159

160  DATA TEST.'SOURCE$'n;

161  SET SASHELP.CLASS;

162  RUN;

ERROR: The MS Excel table SOURCE$ 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.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[Linlin]

Sorry Reeza,

I think I misunderstood your question. my code just update an excel file.

/* create an excel file */

libname test "c:\temp\forum\sasclass.xls";

  data test.list;

    set sashelp.class;

  run;

  libname test clear;

/* drop the excel first before creating a file with the same name */

libname new "c:\temp\forum\sasclass.xls";

  proc sql;

    drop table new.list;

  quit;

/* create a new excel file with the same name */

  data new.list;

    set sashelp.cars;

   run;

libname new clear;

log file:

47   /* create an excel file */

48   libname test "c:\temp\forum\sasclass.xls";

NOTE: Libref TEST was successfully assigned as follows:

      Engine:        EXCEL

      Physical Name: c:\temp\forum\sasclass.xls

49     data test.list;

50       set sashelp.class;

51     run;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.

NOTE: The data set TEST.list has 19 observations and 5 variables.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.01 seconds

52     libname test clear;

NOTE: Libref TEST has been deassigned.

53   /* drop the excel first before creating a file with the same name */

54    libname new "c:\temp\forum\sasclass.xls";

NOTE: Libref NEW was successfully assigned as follows:

      Engine:        EXCEL

      Physical Name: c:\temp\forum\sasclass.xls

55     proc sql;

56       drop table new.list;

NOTE: Table NEW.list has been dropped.

57     quit;

NOTE: PROCEDURE SQL used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

58    /* create a new excel file with the same name */

59     data new.list;

60       set sashelp.cars;

61      run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

NOTE: There were 428 observations read from the data set SASHELP.CARS.

NOTE: The data set NEW.list has 428 observations and 15 variables.

NOTE: DATA statement used (Total process time):

      real time           0.01 seconds

      cpu time            0.01 seconds

[Reeza]

I think that only works because SAS creates a named range when it exports a dataset behind the scene somehow.

If the sheet was already in place it doesn't seem to work.

[PGStats]

You are perfectly right Reeza. When SAS creates a table in a workbook, it also creates a named range with the same name. You can refer to the sheet like this :

libname test Excel "c:\temp\forum\sasclass.xls";

data class;

set test.'list$'n;

run;

libname test clear;

PG

[Reeza]

Thanks! Happy New Years to all as well!!!!

Stuck  @ the office for another hour though....