How to switch between two excel files while recording via app/web recorder

I am trying to copy all the content of first excel file and paste into another excel file. In the second excel file, there’s formula in the second sheet which has filter/formula based on the first sheet. I tried to work with read range sheet 1 of first sheet and write in second file but it’s throwing error.

Hi @Samraat_Maharjan1

Please follow the below xaml and in first use excel file activity mention the first excel and in second use excel file activity mention the second excel. So what are the activities to be done in second excel place all those in inner use excel activity and what are the activities to be done in first excel place all those activities in outer use excel file activity.

sample.xaml (15.7 KB)

Regards

@Samraat_Maharjan1
Hi,

Use vlook up activity to copy all the content of first excel file and paste into another excel file.

I hope this will help you

Hi @Samraat_Maharjan1

You can’t automate excel files by using app/web recorder.

Follow these steps:

  1. Use the “Excel Application Scope” activity to open the first Excel file that contains the data you want to copy.
  2. Use the “Read Range” activity to read the data from the first sheet in the first Excel file. Store the output in a DataTable variable, let’s say firstSheetData.
  3. Use the “Excel Application Scope” activity again to open the second Excel file where you want to paste the data.
  4. Use the “Read Range” activity to read the existing data from the second sheet in the second Excel file. Store the output in another DataTable variable, let’s say secondSheetData.
  5. Merge the data from firstSheetData into secondSheetData. You can use the Merge DataTable activity to combine the two DataTables based on a common key or column.
  6. Use the “Write Range” activity to write the merged data back to the second sheet in the second Excel file.

This will preserve the formulas and filters in the second sheet.

Hope it helps.

@Samraat_Maharjan1

You can use copy paste range with values…that way only values are copied from sheet 2 to sheet1

Basically for that use two use excel file activities with different references and use them in copy paste range

Cheers

@supriya117 in while using write range. what should be value? will it be secondSheetData?

@Samraat_Maharjan1

Yes, pass the secondheetData.

I did but it didn’t copy any value. I have more than 1 lakhs rows which I am trying to copy.

@Samraat_Maharjan1

Please try the above specified activity

Cheers

sorry, but I didn’t understand your method. @Anil_G

@Samraat_Maharjan1

Check the name of the columns of two sheets are same or not.

@Samraat_Maharjan1

You need to use copy paste range…

Or if you need whole sheet just you copy sheet activity

Please check copy paste range activity

Cheers

@supriya117 I don’t have any columns in sheet2. Should I add headers in sheet 2 as well?


My excel file is something like this.


My excel file is something like this.dsa

@Samraat_Maharjan1

Good enough …what is the data you want to copy

Cheers

I want to copy all the data. I just want to paste into another excel of sheet1 because I have used filter in sheet 2 and sheet 3 of another file.

@Samraat_Maharjan1

When you say data even if you want to copy the separate cells present in the top and all then use the above screenshot as is…

If youw ant to copy only the table then use the start range of table and the userange which would be

Instead of Excel1.Sheet("Sheet1").FullRangeName use "B15:K" + Excel1.Sheet("Sheet1").Rowcount"

Here assumption is data start from B15 for table if not change accordingly also the final column is K …if not change that as well

Cheers

I want to copy whole content as it is.

@Samraat_Maharjan1

Then please use as it is as shown in picture

Cheers