Perform action using string in each cell of a row

Hey all,

I have a use case where I am stuck and unsure about the flow of activities which will help.

If you look at the screenshot, for each row which has ‘Active’ in the column P, I need to open Url from the column Q copy text from each cell between B to H and past it on the url.

Example, P2 cell is active. So I open the url ‘www.abc.com’, copy ‘Suresh’ go to the website past it at a place on the website and click add on the website itself, then copy ‘Ram’ go to the same website past it at a place on the website and click add on the website itself and then same with ‘Kabir’

Then the next one which is active is P7, so I will go to url www.def.com and perform the same actions with ‘Laxman’ and ‘Imtiaz’

So, initially, I can first filter column P with Active, then run for loop and for each row and open each url however, after that, I am not sure how to perform the next actions of copying each names from B to H and performing actions. Also note that, the values from B to H are obtained using formula “=TRANSPOSE(FILTER($A$2:$A$35,G2=$B$2:$B$35,”“))” in B column which means we have formulas and we need to copy only values as text and not the formulas

Thank you

Hi @girishankar09 ,

If you’ll use read Range, You’ll get the values only and not formulas.

You can simply use a Filter Data Table on Activate column and then inside a loop
use Browser and pass this as URL currentRow("IVR-Links).toString.
and then read the data from column B to H one by one in a secondary loop, and navigate thru the web application.

Thanks

@girishankar09

I see that you figured most of the logic…and yes when read as datatable you will get values only …

Now coming to gettting all the column values…

You can directly get it using below as a list and can iterate through or use as one

ListName = currentrow.ItemArray.Cast(Of String).Skip(1).Take(7).Where(function(x) Not(IsNothing(x) OrElse String.IsNullOrEmpty(x.ToString))).ToList

This will get all non empty names in a given row…from column 2 to 8(B-H)

You can use this output in forloop and perform the actions

Cheers

Hey @Anil_G Thanks for your time, I tried but I am doing something wrong.

So basically, as per the initial example/explanation, since P2 is Active (current row), it should copy B to H, in this case “Suresh Ram Kabir” into a list variable (example: SkillList) then I will copy this variable data SkillList into a browser and it should past exactly as Suresh Ram Kabir which has single space between each word in the cell

I have attached the main file
Main.xaml (19.1 KB)
what I tried to do, could you please look and correct when I am doing it wrong/incomplete

Thank you Anil as always

@girishankar09

You can use above formula directly in assign instead of the for loop or use the for loop as below…as you need space separated again you neednot create a list…directly can create space separated string

Included code for creating list and then converting to string…commented it out…you can check that too if you want to learn

BH.xaml (24.6 KB)

cheers

1 Like

Thanks anil, will try what you told

I also tried to just use assign and concatenate values and store it in a variable SkillList but not sure why when I run, it gives an error System.ArgumentException: Column ‘B’ does not belong to table sheet. Any idea

Hi @girishankar09

1)To resolve this issue, ensure that you have the correct column name and that it exists in the DataTable you are referencing. Verify that the column name you are using is correct. Make sure there are no spelling mistakes or extra spaces.
2)If you are unsure about the column name, you can try accessing the column by its index. For example, instead of using ‘sheet.Columns(“B”)’, you can use ‘sheet.Columns(1)’ to access the second column (index starts from 0).

Try these both ways. It might work.

Regards,

1 Like

Thanks @Parvathy vathy

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