Read range and invoke if one sheet name or another exists

[jwetherin]

Ok, this is interesting. The simple need is: Every day I will receive reports, the report will ALWAYS have “ComplianceByDepartment” sheet OR “Reporting” sheet. It will never have both plus there will be other random sheets in the file and the sheets may not always be the first sheet. Then I remove various rows via a VBA code. So I am running the below Use Case and it keeps rendering this error

Here is my full sequence

I do a for each file to pick up each file in folder, then with each file I will do an IF in Excel Application Scope. I read the file and look for the first sheet string name and within that IF loop if it is not “CompliancebyDepartment” I then put the “Reporting” string in the else by nesting another IF condition there. If it doesn’t find either (it will always find one of the two sheets) then I do nothing. I save and close the workbooks.

How close am I here or am I going down a way too complicated path? I am doing all I can to learn UiPath quick. It’s a fun tool, but also kicking my tail a little bit.

[Yoichi]

Hi,

There are 3 types of ReadRange activity in UiPath.
Can you try to use ExcelReadRange activity under AppIntegration - Excel instead of ReadRangeWorkbook, as the following image?
It may be necessary to turn on Classic at filter of Activities panel.

Regards,

[sudster]

I would simplify the workflow this way:

[jwetherin]

This switch activity is new to me. Not sure I understand what is happening. You grab current sheet.name, then the “case” compliancebyDept and “case” reporting", then you have it hard coded to have a write line for the “Reporting”.

What happens when a file that has the CompliancebyDepartment?

And given that the reports will never have both tabs, how will this behave. I tried “read range” and if it doesn’t find the range it errors out.

[Anil_G (Anil Gorthi)]

@jwetherin

instead of using loops one thign you can do is to use read range with the sheetname and a try catch around it this way multiple excel scopes and loop alsoc an be avoided

so first use read range with a sheet for departments and in catch use read range with second sheetname…

cheers

[jwetherin]

The problem I am getting with read range is it seems to want to read for both conditions. I don’t understand why that happened.
It’s almost like its now ignoring my IF condition activity

[jwetherin]

What if the sheet name does not exist? Because one will never exist while the other will always. Its either one or the other.

So if i read range workbook.sheetx.xls but sheetx doesn’t exist won’t it error out?

[Anil_G (Anil Gorthi)]

@jwetherin

that si the reason you use a try catch…in try use a read range with first sheet and in catch use read range with second

if first is successful ti would not go to catch if not it will go to catch to try second sheet

Previous activities
Use excel file/Excel application scope
Try
sheet1 read range
catch
sheet2 read range

Next activities

cheers

[jwetherin]

Ok I think I understand. Let me try this.

[jwetherin]

Is this the flow? I think something is off on my build here

[Anil_G (Anil Gorthi)]

@jwetherin

yes this would be the flow with one change…the second should be in catch and not in finally click on add new catch and select exceptiona nd inside that keep the second

cheers

[jwetherin]

Hmm, so can you take a look here? It errors out saying sheet doesn’t exist, but “Reporting” sheet does exist in one of my two files.

[Anil_G (Anil Gorthi)]

@jwetherin

you selected exception as argumentexception…please select system.exception

cheers

[jwetherin]

There it is. Thanks so much Anil, I still don’t understand how this works yet, but it sure does. I’ll submit your comment as solution. Thanks so much