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)
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.
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.
Here whats the purpose of “Element Exists 'title bar TitleBar”?
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.
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.
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.
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).
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?
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
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?
Do you mean like this?
Seems like there’s an error? Do I need to create a variable for sheet?
no need, just ignore that error . The variable is already there
this is correct , just ignore that validation error and “variable not exist” error and click OK
There is an error. Do you know why?
can you upload the xaml and excel file here?
this is because you have hidden sheets in your excel? do you want to skip error checking on hidden sheets?
@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)
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?
no need, you can refer to the new xaml
errorStatus = "Does not contain errors" before for each loop, then remove the
errorStatus = "Does not contain errors" lines in the loop
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
Sub hideSheet(sheetName as string)
If Sheets(sheetName).Visible Then
Sheets(sheetName).Visible = False