Read Dynamic Sheet in excel , but only particular sheets

I have an excel sheet with different sheet names
Eg : 2021 - Ref , 2021 - Ref1 , Ref - 2020 and earlier , Ref1 - 2020 and earlier , Ended , Circulation sheet etc ----> these are the sheet names , year will be varying

I want to read only sheets with years and earlier like 2021 - Ref , Ref - 2020 and earlier and not Ended or circulation sheet

Please Help!!
thanks in advance

@kavya.s16

Welcome to forums

You can use Excel Application Scope and pass the File path with filename of the excel file

Use Get Sheets activity to get all the sheets

Now use For Each activity to loop into the sheets

Now use IF condition and mention as per your requirement

Hope this may help you

Thanks

Hi @ kavya.s16

Define the Sheet as dynamically…
Assign activity

sheetName =System.IO.Path.GetFileNameWithoutExtension(item.ToString)

It will dynamically pick the sheet name…

Thanks

Shyam

Can you please elaborate the if conditions according to the sheets names i have specified.

but case is that i want to read only particular pattern sheet not all.

@kavya.s16

You can write as below

Inside the For Each you can make a If condition and write as

Item.ToString <> “Ended” OR Item.ToString <> “Circulation Sheet”

So define your logic inside Then block
Else block what you want to do if the sheet is Ended or Circulation sheet

Hope this may help you

Thanks

Hi @kavya.s16

Refer this image…

1 Like

but what might be the condition other way round , reference or ended might not be constant, so i want some condition now.year - smc , but thing is i want for all years ,
like now.year - smc = 2022 - smc
what i need is = 2021 - smc , 2020 - smc , 2019 - smc etc

Hello @kavya.s16

Do you need to read the sheets with name XXXX - Ref and Ref - XXXX? Or just the sheets with name XXXX - Ref?

@kavya.s16

Then you can use Regex IsMatch for that

Check below for your reference

image

Hope this may help you

Thanks

there are totally 4 formats
1.2022(year keeps changing) — 2022 - ssc
2. 2022 - smc
3. ssc - 2021 and earlier
4. smc - 2021 and earlier
so basically years keeps varying.

will try this , thank you

Okay, so try this with regex:

In IF activity:

System.Text.RegularExpressions.Regex.IsMatch(“\d{4} - \w{3}”, “\w{3} - \d{4}”)

I hope it helps!! :slight_smile:

1 Like

\d will get the year

\w will get the letters

1 Like