Extract cell details and loop until the end

Help Activities
, ,
1

Hello there,

I would like to ask the bot to do the following and would like to ask for your advice on the steps/ activities that I should use:-

  1. Extract the cell address if “Error Checking” notification pops up.
  2. Keep doing the same thing as per No. 1 by pressing “Next” until the “Error check is complete” shows up.

image
image

Do you mind to guide me on how to do this?

2

@WS_Chai

May I know what is your requirement…as it is not advised to use ui activities on excel we can suggest other alternatives

Cheers

3

Hi @WS_Chai ,
You can get text from browser then
Regards
LNV

4

Hi @Anil_G,

I have for each sheet activity currently, which will press a few shortcut keys to open up “error checking” in every sheet.

image
image940×469 34.3 KB

I would like to extract all error checking results as the bot loops through each error in each sheet, then save them in a variable. I plan to have a write range activity later to write the variable in a summary table.

5

@WS_Chai

  1. I dont think you can get the cell number directly…spy the element and perform get text and then we can use string manipulations or regex to extract the cell number from it
  2. You have to click next till it is disabled…you would know that from the proprties of the next button…check the differences in the properties for next button before disabled and after disabled and use accordingly

Cheers

6

@Anil_G,

Thanks for your reply.

Do you mind to elaborate further on point 1?
Will be great too if you have a sample workflow.

7

@WS_Chai

  1. Use get text on the whole small window and you will get text somehting like

dsfdsf Error in cell D8 adsldals you can use regex as below to get the required value only

System.Text.RegularExpressions.Regex.Match(str,"(?<=Error in cell )[A-Z0-9]*").Value

cheers

8

@Anil_G,

Did I do it correctly?

image
image1006×525 38.5 KB

9

Hi @WS_Chai

image
image1261×659 105 KB

Please check str must be the output of Get Text activity

10

@WS_Chai

Str is a variable that you get out of get text activity

Cheers

11

Hi @Anil_G,

I have changed the strg to variable.
But the text (cell address) still not getting extracted. Did I do it correctly?

image
image1894×709 78.3 KB

12

@WS_Chai

Can you first print and show what is the output of your get text…then we can give the exact regex

Cheers

13

@Anil_G,

Sure, how do i print to see the output from get text?

14

@WS_Chai

Use a log message and give the output variable of get text in it

Or keep a break point wnd open the locals pnel and check the variable

Cheers

15

@Anil_G,

Seems like it prints the title of the box now?

image
image1488×748 66.4 KB

16

@WS_Chai

Did you indicate whole window?

Can you try changing the scraping method in get text and check

Cheers

17

@Anil_G,

I changed to only a specific area instead of a whole box, the result seems to look the same?

image
image1348×688 45.8 KB

18

@WS_Chai

Please change the method

or use get visible text from the classic activities…activities → filtericon-> classic

cheers

19

@Anil_G,

Thank you so much. I can see the the cell address now.

However, I faced 2 issues with this approach:

  1. I set it as a for each activity, when the bot pressed hotkeys for hidden sheet, the “error checking” box still showed the same old result for the last visible sheet. This resulted the same cell address for the last visible sheet got extracted for a few times instead of the correct information of error cells in hidden sheet because they are not visible. May I know how can I overcome this?

image

  1. In one sheet, they may be a few cells with errors, hence we may be able to click NEXT a few times in the “error checking” box. Using the current approach, the bot only extracted the first error cell details per sheet. Would you mind to advice me how can I correct this too?

image
image391×743 6.91 KB

image