Read range_dynamic sheet name

Hi All,

Good day.

Can anyone help me on this error?

below is the sheet name (sheet name changes every time the file is downloaded from the site)
image

Thanks in advance…

#justabeginnerhere

1 Like

Fine
if we dont know the sheet name
then
–in the excel application scope activity we got a output variable workbook and get the output from that property with a variable named out_workbook
–inside this scope now use a assign activity like this
out_sheetnames = out_workbook.GetSheets
where out_sheetnames is a variable of type system.collections.generic.IEnumerable(of string)

–now pass this variable as input to for each loop activity with type argument property as object
–inside the loop use a read range activity and mention the sheetname like this
item.ToString
as item is the variable that holds the sheetname of the excel that we obtain with the assign activity

simple isnt it
hope this would help you
kindly try this and let know for any queries or clarification
Cheers @HILOMAP

3 Likes

Hi,

So the sheet name should match exactly. As this is variable then the sheet name should be a variable too.

My guess is that the sheet name is also the file name. So you can extract the name of the file as variable and then use it as the sheet name. Another alternative would be to change the name, when you download it, to “AccountReport” this is gonna be static for all your files then you can use the same string as sheet name.

Thanks Palaniyapan, i have used an attach window activity to save as the downloaded excel file, then use an excel application scope to read the saved excel file. Will that also work if i previously used attach window?

1 Like

Thanks Samuel:), but here I already changed the file name when i saved it as an excel workbook format (without changing the sheetname which corresponds to the original filename).

@HILOMAP

Do one thing.

  1. Create one workbook variable for Excel Application Scope activity and say varWB.

  2. And pass sheet name like this in Read Range activity.

If you are reading from first sheet.

varWB.GetSheets(0)

2nd sheet - varWB.GetSheets(1)

Etc…

thanks much:)

1 Like

yah this step is perfect
but the thing is we need to access the sheet name which is dynamic and not know as well
so once after saving the excel pass that file path as input to the excel application scope and kindly follow the below steps that would work for sure

you were almost done buddy
Cheers @HILOMAP

1 Like

Okay, i’ll try this one:), thanks much…

1 Like

Fantastic
Cheers @HILOMAP

Hi @Palaniyappan, I encounter below error… do have any idea how to fix it?

1 Like

no worries
–actually we need to pass the out_sheetname variable to a for each activity with the type argument property of for each activity set as object in the property panel
–inside this for each activity only we need to use read range activity with sheetname property mentioned as item.tostring (the variable from for each activity that holds the sheetname from the variable out_sheetname)

Cheers @HILOMAP

Thanks for your patience…

this is my workflow looks like, i’ll update you after doing your suggestion.

image

Fine
the structure should be like
–Excel application Scope and get the output with out_workbook variable
–inside the scope use assign activity get the sheet names with a variable out_sheetname
–next use a for each loop and pass the out_sheetname variable as input
–inside the loop use a read range activity and mention the sheetname as item.tostring

then rest is fine
Cheers @HILOMAP

1 Like

Sorry i haven’t changed the France_BS to out_workbok (But i think it still works, except for below error)

But I encountered another error.

I know now the reason, i just uncheck the add headers field in the property pane, and it worked:)

Thank you so much for your help:)…love it!!!

Really appreciate your help, God bles!!!

1 Like

awesome
Cheers @HILOMAP

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.