Loop on Columns?

What is an elegant way to loop through columns in Excel? Rows are easy to iterate through because we can use something like i as an Integer, and you can just increment it each iteration. However, columns are like “A”, “B”, “C”, … “Z”, “AA”, “AB”, and so on.

I want to write data from “AA” through “AE” for each row. But I cannot increment column names like I could for row names (which is basically numbers), so I am not sure what to do.

Specifically, what I want to do is, I have an array of size 4 (means it can store 5 elements), which stores data. I need to write this data in each cell for a row under column AA through AE. I also need to do this in a loop as the content of array changes each iteration.

Example)
Iteration 1:
myArray(0) = “94”
myArray(1) = “87”
myArray(2) = “100”
myArray(3) = “78”
myArray(4) = “77”

row number = 1

Now, I need to *Write Cell like “AA1” = “94”, “AB1” = “87”, “AC1” = “100” and so on. Then second iteration would be like

Iteration 2:
myArray(0) = “84”
myArray(1) = “47”
myArray(2) = “76”
myArray(3) = “98”
myArray(4) = “61”

row number = 2

Write Cell: “AA2” = “84”, “AB2” = “47”, “AC2” = “76” and so on

How could I get column values in each loop.?

Have you considered using a DataTable to store the values to a DataRow, then you can write or append to the dataset. It might be simpler that way. It also gives you the ability to use index number to reference columns.

However, if you are wanting to increment by the column letter(s) using Write Cell, you will need to convert a numerical value to the letter. Since, this can go to AA and beyond, you will need to use a modulo method to calculate. Check this post for that calculation: UIPath Type Into Excel using alt for formatting issue

Regards.

I am not sure how to convert(?) string array to DataRow…

I certainly have thought about using DataRow, but I don’t know how to add data to a DataRow from string array, which contains data.

Yes of course this can be done @tomato25
–if we dont have the datatable, we can create with a build datatable activity and create required number of columns in it and get the output from this activity with a variable of type datatable
we can use ADD DATAROW ACTIVITY with this variable myArray as input to ArrayRow property and mention the datatable where you want to add these row of data…
–put that add datarrow in a loop where you get the value for this array myArray or next to the activity where we get the value for myArray
–this will add the value of those datarow to the datatable we created…
–then we can use excel application scope with write range to write that data to a excel

hope this would help you
kindly try this and let know for any queries
Cheers @tomato25

2 Likes

Hey.

If you use the Add Data Row activity, you can use the ArrayRow property, which will place each value in the array in their respective column. For example,
myArray can be used in the ArrayRow property and if it has an upper bound of 4, then it will place those values in the first 5 columns. Or, you can do each item individually to organize or skip columns as needed, like {myArray(0),"","",myArray(1)}

Prior to that, you would need to set up a data table though, which you can use an existing Excel file or the Build Data Table activity. In addition to that, you can set an array of column names, and run them through a loop using an Add Data Column, you can add the columns more dynamically; you can also rename them using the index of the loop with dt1.Columns(index).ColumnName = newname. —there is a lot you can do with that as desired.

Regards.

2 Likes

I actually already have an Excel sheet with a table in it, to which I want to insert this data.
The thing is, I am NOT adding a new row to the table. For example, I already have lots of data in the table from column A through column Z. Now, I need to add data to column AB through column AE for each row that already exists.

And the thing is, the Excel file is formatted a lot. There are rows that I can use to specify each row (there is an ID column, and ID never overlaps.), so I can use this row ID to find where each data needs to be added to in column AA through column AE.

But there are lots of colors and merged cells used in this, so creating a datatable from this by using “Read Range” will mess up all the existing format.

So I don’t think I could really use “Add Data Row”, which adds data to a DataTable…

Hence, I feel like just writing directly into the excel file is the only way, and I found Write Cell activity. But it takes forever!

When you use an Excel Read Range, you will have all the data. So, to update all the columns you can run the data table columns through a loop if you don’t know how many columns there are. Or if you know the starting column index, then you can use the myArray count as the index.

Let’s assume you want to start at AB, well the startingIndex will be 27, I think. Then, you can run myArray though a loop and use the index property of the loop as the columnIndex+startingIndex. You will also want to make sure those columns exist in the Data Table, so you may need to use Add Data Column, which will add some additional logic.

Here is the idea:

For each row in dt1
    For each item in myArray // use Output property for index
        Assign activity: row(index+startingIndex) = item

And, you can use a Write Range in there at some point, either for each row or outside at the end

EDIT: fixed Assign activity

The Write Range will keep whatever formatting exists on the file already. If you need to add more formatting with the added values, then, you would need to code some more things in there for formatting.

Can I “Write Range” with “Add Data Row” ???

The Write Range will output the entire data table, so if you Add Data Rows, the data table includes those new rows, and get outputted too

Hmmm, after all, none of these actually works. I cannot use Add Data Row because I don’t have DataTable, and I cannot get one.

I have multiple sheets in this Excel workbook, and I need to write data to column AA through AE in the right row of the right sheet. I don’t think it is a good idea to retrieve DataTable every iteration

I just don’t understand why Write Cell takes so long. It takes 30sec to 1 min just to fill a few cells.

If you use Excel Scope, uncheck the Autosave property. Then, use the Save Workbook activity when you are ready to save. I think the saving slows it down

It can work. If you still want to pursuit this approach, it would be easier to explain how to use a Data Table if you showed an example of what the existing file looks like and how you are updating it, but be specific.

Regards.

Sample.xlsx (12.4 KB)

Sample.xlsx = the Excel workbook with multiple sheets where new data need to be inserted to

I created a mini version of my project with lots of fake data. In my real project, I have many more sheets with more data in each sheet with more columns and rows.
To make it simpler, I colored some cells in blue in each sheet, and that is where new data should be inserted to from MyArray(2). (Note: the content of MyArray will be updated in each loop.)

I now need to insert data to columnE through columnG of the correct row, in the correct sheet. These data come in non-fixed order; It can go back and forth in the sheets, and in the rows.

Since there are many sheets in this workbook with so much bigger data, I don’t think retrieving datatable from each sheet just to insert new data is a good idea. So I am using Write Cell, but it only fills one cell at a time. So to fill 3 columns per person, that already takes lots of time…

1 Like

I’m trying to understand better, so I can make a good answer.

Do you know which Month to update during your process so you have the sheet name? or do you need it to choose the latest month automatically? Although, I think it would work better if you knew what month the date gets updated to.

Are all the “Score” columns in the file already or do you need to add more based on how many values are in myArray?

Will the rows be in the exact order every time, or do you need to match the row with the person’s name or number to ensure the Scores go on the correct row?

Will there be rows that need to be added to the existing data or all people are already populated? This would also mean you need to add borders to the added row.

Sorry, I don’t mean to ask so many questions. :thinking:
The automation can handle a lot more than people think sometimes.

Typically, I would skip the myArray step, though, and go directly from where the data originates from to the excel file.

Assuming, the columns and rows are all static like in your example, I can provide you a sample to simply update the Scores dynamically. I’ll get back to you on that part in a bit.

Regards

1 Like

Here is sample workflow showing how you can implement some Data Table automation:
tomato.xaml (20.9 KB)

This is the output, where I used the same 3 values in the scoresArray for each person but the array can be easily changed during a loop to update each person.
image

Also, read the annotations and unique activity names to get a good understanding of the steps and variables used.

I set the sheetname based on Today’s date, but a different date can be used.

Other than that, I would need to know more about your process to make further suggestions. Like if you need to match the row with the correct person, et cetera.

Regards.

1 Like

I really appreciate your help ClaytonM. You have done so much for me, and it certainly did what I wanted to achieve.
Also, I didn’t know until I checked your program that you could add annotation to variables. I learned a lot from your program. Thanks for that as well!

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.