Copy data from excel and paste issue

Help Activities
1

Hello All,

I would like to create my first sequence but I have a problem. My purpose is to filter an excel workbook, copy certain columns from the filtered datas and paste them (into an another file or another sheet). The problem I have faced is according to UiPath, pasting is not possible due to the different size of the source and destination area. But in reality, thesdimensions are the same.

Could you please help me how to solve it? I am a totally newbie so sorry for the noob question :slight_smile:

Thank you very much!

Zsolt

2

Assuming you don’t need to copy over colors and cell formats, can you try

  1. read range

  2. filter columns and rows using filter datatable activity

  3. write range of filtered datatable on the new tab

3

Hello,

Thanks for your help but it looks it’s not working. For better understanding, I have created a sample file to expalin what I would like to do:

image
image1039Ă—173 5.93 KB

I would like to filter these datas: column H is empty, responsible persons are Tom and John. This is the output which should be copied and pasted.

But unfortunately I got an error message at Copy paste range Error message is Hungarian, Paste is not possible due to the differetnt size of copied and pasted area.

Could you please check what wrong is?

Thank you very much again.

Zsolt

4

And an another importatnt thing: here is a screenshot about Copy paste activity:
image
Source range is column D and I would like to paste the datas into a new worksheet (best way would be a new file but I guess this is the simpliest way).

Thank you again!

5

If you change the destination cell to A1, it should work. Reason being entire column D cannot be fit into A2 onwards since there is one fewer cell.

6

Thank you again, it works - except filtering, because I set the criteria column H is empty. But unfortunately robot copies and pastes all rows, not only the empty ones.

My Filter activity:
image

7

Hi @Zsolt_Marton !
What about using write range instead of copy paste range ? This way you can have a new excel file :wink:
Here is a suggestion with write range: Copy_paste_with_conditions.xaml (8.1 KB)
Here is the excel file I tried with: abab.xlsx (8.8 KB)
Here is the result after running the code: other file.xlsx (7.3 KB)

Let us know if it does not work as expected, or if you need more details on the code !

8

Hi @Hiba_B !

Thank you very much, it works well :slight_smile:
But, is it possible to export the data into an already prepared file? I attach my idea; I don’t need all of columns from the source file and their queue would be a bit different. Furthermore, I would like to keep the header of the output xls, if possible :slight_smile:

In the output file, you can see the columns of Shelf ID and Description as a new ones which would be fulfilled manually. But I don’t need Resp. person and Date.
other file.xlsx (10.7 KB)

And I hane an another question regarding coding: I have learnt that Excel application scope would be the first step but you didn’t use it. Nevertheless, your solution is perfect but could you please explain the reason behind?

Thank you very much again :slight_smile:

9

Hi @Zsolt_Marton !
Sure, here is a new suggestion: Copy_paste_with_conditions.xaml (13.0 KB)
So now when you run it, the bot reads:

  • other file.xlsx (to have the structure of the datatable)
  • abab.xlsx file to have the source information
    Then the bot writes in other file.xlsx with all the needed information, taking into account the 2 new columns. Whatever might be the order of the columns, the bot will find the right column to fill it correctly :wink:

To answer your question: to read an excel file, we have two options:
image
We can read range by system or by excel application scope.

  • Read by system means that the bot does not need to open excel to have the access to the data. This option allows to read xlsx files (maybe other extentions as well, but xlsm, xlsb no). If you run the bot and your excel file is open, there is an error: the bot can’t have the access to the file, so you will have to close the file.
  • Read by excel application scope means that the bot has to open excel (in background or foreground) to have the access to the data. There is no restriction to the extensions, all excel formats are supported. You can’t use read range excel without excel application scope. If you run the bot and your excel file is open, there is no problem.

The same logic is used for write range (you have write range by system or excel application) :wink:

10

Hi @Hiba_B !

Thank you again, but there might be some problem, a component is missing(?)

image
image676Ă—513 12.9 KB

11

Oh ! What version are you using of Studio ? As I am using 21.4, you need to upgrade to this version to see what I wrote
Otherwise I’ll send you several screens

12

Here is the screenshot of the missing part:

image
image1504Ă—760 46.4 KB

So you need to drag & drop:

  • foreach row activity
  • add datarow activity
  • multiple assign activity (or drag & drop 5 assign)
13

Hi @Hiba_B !

Wow, thank you very much, perfect! :slight_smile: Robot is able to find the approriate column and paste the datas. Not relevant columns / cells are empty.

I will mark your previous answer as solution but just for better understanding, what if one cell is NOT empty in the second row, for example contains a formula, or whatever? Can the code insert the datas into the second (third, …) rows, nevertheless the filled cells?

In fact, I am using the version of 2019.10.4. Upgrading is not possible, I guess.

Thank you again :blush:

1 Like
14

Hi !
Great to hear that it works for you !!
When we use read range (being it system or excel app) the bot takes the data (or result of the formula), not the formula.
To keep the formula it’s a little bit more complex haha as we should deal by cell and not by row :grin:
Do you have formula in your case ?

15

Hello Hiba!

My formulas are not too complicated: there is a price in column H which should be given manually and formula of cell I2 (I3, I4, etc…) is =H2*C2.
Furthermore, I have an another formula in column J, formula of J2 is =I2-G2

Thank you very much for your efforts again :slight_smile:

16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.