Dynamic Variables to reference active cell in excel while using for each loop

Help
,
1

Hi,

I am struggling to understand how to dynamically reference an excel cell while in a ‘for each row’ loop.

For example, I am looping through each row in a column of dates and am using an if activity to decide if the date is < 01/01/2017. For all the dates that are < 01/01/17, I would like for their cells in the excel file to be changed to 01/01/2017. My main issue is finding out how to tell the ‘write cell’ activity to update the active cell while looping through.

How can I use a variable to have the current row also be used as the excel cell I want to change?

Here is my current workflow

Capture
Capture510×900 21.8 KB

2

Here is my example data as well

Capture2

3

Hello.

There is another approach, which can be more reliable I think, where you take the entire data table and change the value in the data table, followed by a Write Range at the end which would overwrite the file with the changed data.

However, I will first give you an idea on how to use the Write Cell.
To get the row index so you can use as the row number, there is some dot net syntax to use:
DateCol.IndexOf(row)
Then you also need the column name. You can hardcode it as “B” or use IndexOf again and convert to a character. However, the IndexOf would only work with a Data Table variable. (for example Convert.ToChar(dt1.Columns.IndexOf("Date")+65).ToString

Then, concatenate that together for the range.

Sorry, if I may have got the above wrong as I have not confirmed those examples as I’m posting this.

The other more reliable approach would be to use a Read Range instead of Read Column.
Then if you run a For each loop on that table, you can reference the Date column and change the value directly inside the data table variable. Then, follow that with a Write Range after the loop.

For example:

Excel Scope
    Read Range // let's store in dtData
    For each row In dtData // use DataRow for TypeArgument if you use generic For each activity
        Assign row("Date") = If(IsDate(row("Date").ToString.Trim), CDate(row("Date").ToString.Trim).ToString("MM/dd/yyyy"), row("Date").ToString.Trim)
    
    Write Range // using dtData

I didn’t include a condition for < a date, but if you include that in there, make sure you always check if it’s a date (with IsDate()) to avoid future errors down the road.

Regards.

2 Likes
4

Thank you so much. My finished product works perfectly after a few adjustments.