How to make excel cell ranges dynamic

[raju_alakuntla]

Hi,

I want to send the excel table of certain ranges not the entire sheet in a mail . But everytime the cell ranges increases.

for example, from the above screenshot, I want to send the table of B3 to D7 but for next time the cell ranges increases.

How can we automate it.

Regards,

[rlgandu]

@raju_alakuntla

In write range workbook in “range” property mention the range you want to write.For Example you want to send the table of B3 to D7 then write “B3:D7”

[Quenton_Wayne_Rebello]

If you want the entire table that is B3:D17. Then when using read range workbook, you can set range as B3 which reads everything from B3. Later use a filter table activity to remove rows if it is empty

For Read Range Workbook

For Filter DataTable

[raju_alakuntla]

But for next execution, we are adding rows to the table so that it increases both starting cell and ending cell. May be B4 to D9 like that.

[raju_alakuntla]

Here my task is to select the tables either Table1 or Table2 or table3 irrespective of their ranges either the starting cell or the ending cell or both increases.

[Brian_Mathew_Maben]

@raju_alakuntla I have made a test workflow using a sample excel. Basically this will isolate the tables based on empty cells

Main.xaml (16.5 KB)
Input.xlsx (9.6 KB)

1.In the ‘For each Row’ use an ‘If’ activity to check if the current row is Null or not ( Not String.IsNullOrWhiteSpace(CurrentRow(0).ToString) ).
a. If a value is present then convert the datarow to an array(CurrentRow.ItemArray) and add it to another datatable(eg: dt_output. Build this datatable with the necessary columns).
b. For the ‘Else’ condition(when datarow has Null elements) you can Write the above datatable(dt_output) to an excel file and perform the mail operations and then clear the datatable(Invoke Method).
c. Also make sure the ‘Else’ condtion has an ‘If’ activity to check if the datatable(dt_output) has rows present in it before performing any operations.
d. In the above workflow after the for each activity I have added another If condtion to check if dt_output has rows present. In the final iterations the loop is exited and it wont update the excel, therefore this step was necesssary.

Make sure to delete the output excel file after each iteration. Let me know if it works!

[raju_alakuntla]

Hi,

Actually I need to send each table separately to each individuals like table1 to X person, table2 to Y person and table3 to Z person. I am using read range and sending the table with the cell ranges but for next execution the table cell ranges increases by adding new rows to it. So I am not able to send it properly as I have used fixed cell ranges in read range. Now I need to make cell ranges as dynamic.

Regards,

[Brian_Mathew_Maben]

@raju_alakuntla Yeah since each table is separated by an empty row, when you read it using the read range activity the datatable will also include the empty values.
So you can Isolate the tables based on this empty value condition

In the ‘Else’ block of the workflow I sent, you can perform the mail operation

Workflow for reference
Main.xaml (16.5 KB)

[raju_alakuntla]

I am getting this error

I used this excel sheet

Regards,

[Brian_Mathew_Maben]

@raju_alakuntla This was just a test workflow, it adds three items into the datatable at a time. The input excel used for this was:
Input.xlsx (9.6 KB)

Since your input excel has a different structure, it is having problems writing the information into the datatable.

In the range options under ‘Read Range’ try typing “B:D”, since all your data is present in the columns B-D