Copy dynamic excel range

Help
,
1

Hello All,
I am trying to copy a excel range (2 columns and X rows) that can change from one worksheet to another. I have no idea how to make a “do Until” loop in UiPath to find the range that I want, so to make it easier I made a excel Macro to fill two fixed cells in the worksheet with de First row and other with the Last row that contains my data and this data is always in the same column.
So my question is: How can I make a selection in a Range that is determined by a cell. For example: My “D1” cell contains a number that refers to the first row of my range (x) and cell “E1” contains the last row (y). And I want to select cells “Ax:By” and copy it (CTRL+C).
I don’t know if I made myself clear, any doubts please let me know.
Thank you in advance.

2 Likes
2

Well there is a Range property for activities like Select Range or Read Range. So you can place that number in that field as a string, like “A”+cell1.ToString+“:B”+cell2.ToString , with cell1 and cell2 containing the value from the cells.

In order to get those values, you will need to use Read Range or Read Cell and either store the values into a variable with an Assign activity or use the object directly in the Select Range or Read Range, like dt1.Rows(1).Item(3).ToString

If you use Select Range, then follow it with a Send Hotkey or TypeInto “[d(ctrl)]c[u(ctrl)]”
If you use Read Range, then simply use Write Range to place that range into the spreadsheet if that was your goal.

Side note: you can use the Do While just like the Do Until but use the opposite for the condition, and there are various ways to find the range you are looking for without an external script or macro.

Does any of this help?

Regards.

5 Likes
3

Thank you ClaytonM! That was exactly what I wanted.

4

If I may explore your knowledge just a little more :smiley:, I have to do this task X times, based on the number of tables that my worksheet has. Again, one cell in the worksheet contains the number of tables, lets say cell “C1” = 4, so I have to copy and paste 4 different tables using the procedure you explained. How can I make, in UiPath, a loop that repeats X times?
Thank you again.

5

Hi.

There are a couple way to accomplish your loop.

If you have an array of values, you can use a For each on that array to process each table. So if you were to store the ranges in a string like “A1:B1,A2:B2,A3:B3”, then split it like ranges.Split(","c), now you have an array to loop through. You can use various arrays to accomplish this like even an array of your sheets which you can get using a Workbook variable like wb.GetSheets().

Creating this array will depend on the data you are looking and determining the best method to construct it.

The other method would be to use your “4” value as a counter inside a Do While loop. This is sometimes easier to understand.
So, it would be something like this:

Read Cell
tableCount = If(IsNumeric(cell.Trim), CInt(cell.Trim), 0 ) // Store "4" into a variable

Do
    <process table>

    tableCount = tableCount - 1

While tableCount > 0

Regards.