Exporting dataset into pre existing Excel template

[sas1011]

Hello All,

Its my first question in this community so please pardon me if I missed out information that could be vital for a satisfactory response.

Background of concern:  I am currently running a program that generates a report. I want to export the generated datasets into an Excel workbook that already exists(This does not contain any data- it just includes the column names and sheets. I need to fill this sheet up with the data generated through my SAS Program). 

Status quo: Right now, I am exporting it into a new workbook, and then manually copying and pasting the values into the excel template workbook.

Q: Is there an option that I could use, that would allow me to export my dataset directly into the Excel template? 

P.S: I tried ods excel and specified the Excel template workbook name as the filename - but it replaces the formats and columns on the template with that of my dataset column names. 

Thank you.

[SASKiwi]

If you have a highly-customised Excel template then the best way to refresh this is from SAS is to write your SAS table to a separate "vanilla" data sheet, then have formulas in the template pointing to the data sheet to pick up the new data.

PROC EXPORT would be a good way of doing this.

[Reeza]

I concur with @SASKiwi, the easiest method is to create another sheet with the raw data in a clean unformatted way. Modify the template to link and format the data from this sheet in the actual report. 

Then when you replace that data the template is automatically updated. 

[art297]

I don't want to get your hopes up but, if the exportxl macro will work on your system, it can be used to do the job. It was designed so that it can take advantage of both an excel template and/or using an existing excel workbook as a template. You can download the both the macro, and its documentation, at: http://www.sascommunity.org/wiki/Excelling_to_Another_Level_with_SAS

Art., CEO, AnalystFinder.com

[sas1011]

Thanks all.

I think what Art mentioned got me closer to where I wanted to get, if not completely.

I will keep trying for more ways but all your answers did help me. 

[art297]

If the macro worked on your system, post the code that you used to call the macro and let us know what you weren't able to accomplish.

Art, CEO, AnalystFinder.com