Update a given excel sheet with new data

[azorachase]

Hi,

I have a worksheet that I need to update monthly in excel. The excel sheet is linked to a word document; thus updating the excel sheet also updates the table in my word document.

I would like to use SAS to update the data in excel each month. The headings always remain the same.

I am using SAS version 9.4. Is there a way of replacing the data on a given sheet? To clarify, I do NOT want to overwrite the entire workbook or append data to a new sheet, as this will interfere with my ability to automatically update my word documents.

Thank you so much for your help.

[Reeza]

Do you have the Add In for Microsoft Office?

[Reeza]

Otherwise your best bet is to set up a named range in your excel sheet. Make sure it's large enough to hold all of your data. 

Then you can assign a libname to the workbook and drop and replace the information in place.

untested:

libname out xlsx 'path to xlsx file';

proc sql;

drop table named_range;

quit;

data out.named_range;

set new_data

run;

libname out;

[PGStats · Accepted solution]

This works for me:

/* Write new data to Excel workbook containing a 3-column named range called MyData */

/* Create some data (3 columns) */ 
data myDataUpdate;
do i = 1 to 10;
    x = i;
    y = i * i;
    z = i * i * i;
    output;
    end;
drop i;
run;

/* Connect to the workbook */
libname xl excel "&sasforum\datasets\myDataUpdate.xlsx";

proc sql;
/* Drop the contents of the named range */
drop table xl.MyData;
/* Write new content to the named range. 
 The number of rows in the named range will be adjusted  */
create table xl.MyData as
select * from myDataUpdate;
quit;

libname xl clear;

[azorachase]

Brilliant, thanks!

[RW9]

Well, several points here.  First why do you need the Excel file if your using Word?  Secondly Office has VBA behind the scenes, dump the SAS data to CSV, then use VBA to import and process the data into either Word or Excel.  Finally, why bother using office at all, if you need to create reports then do it straight from SAS (HTML maybe?) or use a proper reporting environment.