Mutiple Excel files into a single excel workbook

[hari1109]

Hi all,

I have a sheet in an excel work book and i would like to add another sheet as sheet 2 to the existing workbook at some point of time later (may be a month after) and in such a way i will be adding sheets generated using Proc report to the existing workbook. Is there any way to achieve it. Here i don't have a specific number of sheets in hand to write them off to the single workbook, i will be running my code to generate (using ODS TAGSETS.msoffice2k ) a new sheet every time and i have to add this new sheet to the existing workbook which may or may not be having sheets in it.

Thank you all.

[art297]

see: http://www.sas.com/events/cm/867226/IntroExcelXPHandout.pdf

and http://www2.sas.com/proceedings/sugi31/115-31.pdf

[hari1109]

Hi ART,

I have gone through those and are helpful but my issue is different , here i have to add an excel sheet every month and i have to put that sheet in the exisitng excel file. I have an excelfile with one sheet from the previous month and now i would like to add another sheet as sheet 2 to the existing excel workbook. In the same way i will be adding sheets as (sheet 3 ,sheet 4,...,sheet12 )to the existing excel workbook month after month. one way i am trying to do is to direct all the data sets to a location and then using proc datasets procedure to copy all the datasets from the pervious and current month to the work library and then using ods tagsets to create mutiple sheets in single excel work book.

Thank you.

[SASKiwi]

Using the ODS tagsets approach to building an Excel workbook requires that you create the workbook from scratch each time. You cannot add to an existing workbook. This is because ODS is not creating a native Excel wookbook but is creating an XML or HTML file that Excel knows how to interpret as a spreadsheet/workbook.

There are three choices I can think of:

1) Keep all of  the monthly datasets you need to create a complete workbook and use the ODS approach to create it completely from scatch each time.

2) PROC EXPORT can add a sheet to an existing workbook using the SHEET statement. Creates a native Excel workbook.

3) Use the LIBNAME EXCEL engine to do the same as 2).

Each of these choices has advantages and disadvantages. You will need to decide what suits you best. 

[Ksharp]

Add one more thing into SASKiwi.

ODS msoffice2K can also add a new sheet into a existing xls file. But that is not pure xls file.

Ksharp