UIPath - Iterating through excel file to fill in DataTable with Read Range “dynamically”

Question

my question is in principle the same target as Looping on URLs from Excel file using UiPath (in the sense of reading in an Excel File with ReadRange an Iterating through it)

I want to iterate through the excel file - and would, of course, do this with an Excel Application Scope. But when I read in an Excel File into a DataTable - I need to give this a range like "D2:K30"..

The "K30" would be the end of my range read in - so how do I manage to get the "end" of my specific excel file?

With VBA I would just iterate through the excel file and say something like

'if row content is ""'

, and then i would know when the first EMPTY row appears.

Maybe my first found empty row would be saved in Variable "emptyRow" - then I could say

Read Range "D2:K" + emptyRow - 1

So my target is to make this Read Range 'dynamically' in the sense of not copying empty rows into the datatable.

Because furthermore - when I copy that datatable into another excel file - I want to color specific ranges with different colors, therefore I need the specific length of each range, otherwise I will get empty rows colored as well.

Answer 1

There are various ways to solve your problem, the easiest I can think of is read range for the entire sheet and find first empty cell then you'll have all the things you need in the data table and manipulate it as you like. This method is very inefficient but easy to implement.

Another way you could deal with this is by invoking VBA, you can do it something like this :

Function GetRowCount()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    Dim k As Long
    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count
    GetRowCount = k
End function

This should work just fine, but it requires you to use an Excel scope (and this requires you to have Excel installed).

hope this helped