Saving data in a single excel sheet back to back

Help
1

How can we save the extracted data from multiple workbook subsequently in a single excel sheet…

2

Hi @sreesudha.oc,

Are the excel data rows constant each time when you copy and paste?

3

No rows are different.

  1. Excel sheets which we have to extract a specific set of data is different. Eg : For eg i need to extract amt>1900 from 5 different excel saved in a folder.
  2. Once filtered , i need to paste all output sequentially in a different excel. For eg : If my first excel gives an output of 10 items and second gives 20 items. These 20 items has to get pasted below this 10 items which i have already extracted.

Doubts:

  1. How can i copy paste the data sequentially.
  2. How can i call the subsequent excels and filter this data.
1 Like
4

You can check for Append Range Activity.

Once you added first excel data to new excel, save the excel (close excel application scope here) and go for append range ( in next excel application scope).

1 Like
5

Hi @sreesudha.oc

You can use two activities here…

  1. Write Range
  2. Append Range

Write Range
If not configured property, it will basically replace the excel sheet with new data. But, if you explicitly specify the row that you need to start writing, it is a good choice.

In your scenario,
1st part, you extract 10 records and write 10 records using Write range. Next part you have 20 records to write, just below the 10 records you added to the excel.
What you can do is,

Get the excelsheet row count to a variable in an assign activity. You can do this by adding a read range activity to read the excel you are inserting data to. The extracted data will get stored in a datatable. Then you can get the count from there using below

Counter =  dt_excel.Rows.Count

Then in your Write range acitivity, in the range property, You can specify as below
"A"+Counter
This way, it will add the data to the excel sheet without overwriting data and in the required sequence.

Append Range
This is the other activity which you can use to add data sequentially to the excel sheet. What you need to do here is to, just pass the datatable you need to write to the excel and it will add the data just below the existing data without overwriting…

Does it help?
let know if you need any further assistance in this regard :slight_smile:

6

@sreesudha.oc

1. How can i copy paste the data sequentially - Once you filter with your relevant criteria, use a click activity over the first cell you want to copy. Then insert a Send Hotkey activity and perform ctrl+shift+left arrow, then again ctrl+shift+down arrow. It will select data you filtered and then use ctrl+c using same Send Hotkey activity

2. How can i call the subsequent excels and filter this data - You have to use Excel application Scope for each excel and perform the operation. For filtering you can use hotkey alt+h+s+f, then click on drop-down. Then use a click text activity and click the desired filter you want.

If you face the issue still, please reply your queries here

7

Hi,
when I use Append Range, it works, but adds the data many times. Tkanks to that instead of 1 row , I have 11. What should I do?