Filling Out Excel Sheet

Help
,
1

Hello,

I am currently looking on an automation tool that types in pulls an address from an excel sheet and then searches it up in an online database. From that database it pulls certain information and I would like it to populate the excel sheet. We already have information for certain addresses, so we would like it to skip those addresses. The problem that I am having is that so far I’ve only been able to fill out the same row over and over again, but I would like it to go to the next rows and start filling those out. I was wondering if there was a way to do that. An example of how my table is below:

Address Info 1 Info 2 Info 3
A
B X X X
C X X X
D

So in the example above, I have it that I already have information from B and C, so I would like it to skip those rows and only search for A and D and then fill out columns for each respective row if it is possible. One of the tools that I was using was the “Write Cell” tool, but unsure if there is a better one to use or better approach to it. Thank you in advance for your help!

2

Hi.

I would not normally suggest using the Write Cell because it requires a cell location and more logic to use consistently.

If you can store your entire data into a data table, then you can manipulate the data however you want. Then, when you want to update the data in the file, you can just write that data back to the spreadsheet. —this also has disadvantages too, however.

You can use the Read Range activity to store the data to a DataTable variable. (in my example, I will call dt1)
Here is psuedocode of some logic you can use:

Excel Scope activity
    Read Range //store to datatable variable
    For each row in dt1
        If row(1).ToString.Trim = ""
            <perform actions>
            Assign activity: row(1) = newvalue
            Assign activity: row(2) = newvalue2

    Write Range

The disadvantages is that when you Write Range, the spreadsheet keeps its existing data and you just overwrite it, so if the existing data on the spreadsheet is longer than what you are writing to it, you will see the extra rows still there (but clearing the file before writing can fix this, but loses formatting). —since you are not removing rows or anything, it shouldn’t be a problem for you.

Alternatively, you can take a more efficient approach to processing specific rows by using Filtering in which there are a few various ways to accomplish this.
Here is an example psuedocode for that as well:

Excel Scope
    Read Range
    Assign activity: rowsFiltered = dt1.AsEnumerable.Where(Function(r) r(1).ToString.Trim = "").ToArray
    For each row in rowsFiltered //TypeArgument DataRow
        <perform actions>
        Assign activity: row(1) = newvalue
        Assign activity: row(2) = newvalue2

    Write Range

So give those ideas a try.

Regards.

1 Like