I have about 50 excel files with 4 sometimes 5 or 6 worksheets or tabs with about 10-20 Columns.
- SPI, ECR, WCR, AOT worksheets in every excel file.
- The first row contains the field name.
- The name of the excel file is a date or week of a date. e.g. wk40.xls ( week 40)
I want to append All Tabs from the 50 excel files into 1 excel file, but i want to create 2 new columns that will contain the Week and Type(the name of the Worksheet).
e.g. week Type Column1 column2 column3 etc..
40 SPI 51.88 01/01/02 33
40 SPI 22.48 01/05/02 15
40 SPI 48.15 01/03/02 4
40 ECR 18.14 05/04/02 77
40 ECR 9.99 02/05/02 25
40 ECR 71.04 09/11/02 1
I want to populate the week column with the name of the file and Type with the name of the worksheet or Tab in Excel and i want to append all these into 1 master excel file. All the files are in the same folder. how can i do this programmatically?
Thanks in Advance,