For each sheet is giving incorrect result

Help Activities
, ,
1

Hello there,

I would like to repeat the same steps for every sheet in an excel file.

For example, I have an excel file with 4 sheets in total, 1 of them contains error that the robot should highlight.

The correct result in the log message should show 1 sheet contains error and 3 with no errors.
However, when I run my workflow, the result is incorrect and shows all 4 sheets have no errors.

Does anyone has an advise? Below is my workflow, look forward to your guidance.
Thank you in advance.

SheetWithError.xaml (17.4 KB)

2

Hello @WS_Chai ,

Could you please explain the requirement here. From the Workflow which you have attached, you are getting the Excel sheets and using a forloop to get into each sheet and you have used an Element exists activity. If you can explain the actual requirement we canresolve this issue.

If you are trying to read some data from Excel, suggesting to Use "Read Range "Activity.

3

Hi @Rahul_Unnikrishnan,

I wish to click the “Error Checking” excel function in every sheet to identify if there are formula errors.

If there is, I would like the robot to indicate it contains error in the log message and vice versa.

4

Hello,

Here whats the purpose of “Element Exists 'title bar TitleBar”?
image

Whats you can do is Instead of using the above activity You can declare a variable ErrorExists as boolean and assign “false” as default value. After applying the send hotkey, you need to check “Element Exists” that error message and use variable “ErrorExists”. The it will work fine.

In your case you have provided the ElementExists before the Send Hotkeys.

5

Hello @Rahul_Unnikrishnan,

I have amended the element exists to after send hotkey, but the problem still persists.
It still indicates all sheets have no error, while the correct result should be one contains error.

image
image871×758 24.2 KB

image
image865×797 31.3 KB

image

6

Here the Element Exists should be tagged to the Error window and nOt on the “Error Checking” Tab. Please try as below. It should work.

Steps:
Send Hotkey Alt+m
Send Hotkey K
Send Hotkey Enter or You can use Click Activity on the Error Checking.
Element Exists on the below Window.(Image below). If True, (there is no error ) , If False (There is an error).

image
image888×268 15.9 KB

7

@WS_Chai try this…
SheetWithError.xaml (29.2 KB)

8

Hey Jack,

Thanks for guiding.

May I know how did you do this?
I wish to start off the process from the very first sheet every time, however the first sheet of every excel file has a different name instead of “Sheet 1”. Do you have any tips?

image
image1520×756 41.7 KB

9

no it doesnt click on sheet1, it clicks on whatever the sheet name is in the loop

you can check the selector of the click activity and see that its using the sheet variable

image
image1044×285 25.9 KB

10

Hi @Rahul_Unnikrishnan,

I have tried to amend it, however the result is still not reflecting the errors for each sheet correctly. Anything went wrong with my workflow?

image

image
image778×821 31.2 KB

11

Hi Jack,

Do you mean like this?
Seems like there’s an error? Do I need to create a variable for sheet?

image
image1681×373 12.2 KB

image
image752×1016 27.1 KB

12

no need, just ignore that error . The variable is already there

13

this is correct , just ignore that validation error and “variable not exist” error and click OK

14

Hi Jack,

There is an error. Do you know why?

image
image1557×802 52 KB

15

can you upload the xaml and excel file here?

16

Hey @jack.chan,

Attached is the workflow and excel files.
Thanks for helping :smile:

OpenAuraFile (Hidden Tab + Errors from Aura downloaded files) - Copy (1).xaml (63.7 KB)
A filefortest.xlsx (10.3 KB)
New Microsoft Excel Worksheet.xlsx (9.7 KB)
Summary.xlsx (8.7 KB)

17

this is because you have hidden sheets in your excel? do you want to skip error checking on hidden sheets?

18

@WS_Chai you can try this, i added logic to skip the error checking in hidden sheets
OpenAuraFile (Hidden Tab + Errors from Aura downloaded files) - Copy (1).xaml (71.4 KB)

19

Hi @jack.chan,

Thanks for the prompt reply.
I wish to check the errors in hidden sheet too.

Also, can I consult you:- if any of the sheets have errors, I wish to highlight this in the Summary file I have with a remark “Contains errors”. Unless all sheets (hidden or visible) are free from error, then the Summary file should indicate a remark “Does not contain errors”.

Currently, my ErrorStatus variable does not have this condition set. Shall I use if condition?

20

no need, you can refer to the new xaml
I set errorStatus = "Does not contain errors" before for each loop, then remove the errorStatus = "Does not contain errors" lines in the loop

new xaml
OpenAuraFile (Hidden Tab + Errors from Aura downloaded files) - Copy (1).xaml (71.2 KB)

and also add this code to your “Macro Script.txt”, this will unhide sheet if hidden (so that it can be clicked) then hide the sheet again after its done

Function unhideSheet(sheetName as string)
    If Not Sheets(sheetName).Visible Then
        Sheets(sheetName).Visible = True
    Else
        Debug.Print "vis"
    End If
End Sub

Sub hideSheet(sheetName as string)
    If Sheets(sheetName).Visible Then
        Sheets(sheetName).Visible = False
    Else
        Debug.Print "invis"
    End If
End Sub