Read Multiple Excel Sheets

Help
1

How to Read the Multiple excel sheets values.

I have a excel file with 2 Sheets (Both Sheets Columns are same) .
I need to write these sheets values into another excel file.

For example →

File 1: (Input File)
Sheet 1 : 6 Rows and 5 Columns
Sheet 2 : 3 Rows and 5 Columns

File 2:(Output File)
Sheet 1 : 9 Rows and 5 Columns

Can someone help to achieve this.

2

You simply need to read them as separate instances. Use Excel Application Scope and Read range within that for the first sheet and then repeat the activity for the second sheet. I’m not sure if you can simply append the 2nd sheet to the first datatable, if not read them as two datatables and then merge them using the Merge Data Tables activity (this will work as columns are identical).

Then use write range activity to create the output file.

3

If you don’t know the number of excel sheets from which you might need to pick the data then you can use loop for this. Define an index and you can read the Sheet with something like (“Sheet” + index.ToString). If the index is 1 then use write range activity and if it is greater than 1 then you can use append range activity to merge all the data in 1 sheet. I’ve attached a sample workflow for reference.
ExcelAutomation-MergeExcels.xaml (8.1 KB)

1 Like
4

its excel 2016,
I tried but lastly one is missing
First it reading temp.xls file from Sheet1 and its pasting in write range activity of first sheet of Eiffel.xls file.
and second time its reading same temp.xls file from sheet1 only but in write range its not pasting.
What i have to change in the code.Please guide me
Initilized index with i = 0;

excel1
excel1527×795 15.6 KB

5

Hello Mohit.

In lieu of this solution, if we have multiple sheets within the same excel but the sheets which all have different data in different positions, how can we extract the data out of them inside a diffrent datatable.