How to use a variable as a sheet name in the Excel Read Range activity

Help
, ,
1

Hi All,
I need the read range activity to use a variable as the sheet name to look up. When using the variable name as the sheet name in “Read Range” it doesn’t seem to work.
Could someone please advise how I can use a variable as the sheet name in Excel activities e.g. Read Range?
I am copying text from an Excel scope list based on user input, in this instance it is the name of the country so I have set the variable:
image
I then wish to refer to this variable as the sheet name in Read Range:
image
This works when I manually type in the sheet name and I have tested that the variable works using other activities but I can’t seem to get it working with Read Range.
Thanks in advance for your help!

1 Like
2

Hello!

Just add a .ToString() on the final of variable and will work fine.

Example: CpyCountry.ToString()

Regards,

3

Thanks Lucas. I tried that also and it doesn’t seem to work. I am receiving the below error message:

image

Thanks for your help.

4

Try to check what values your variable are receiving and check manually if matches with the actual excel sheet.

I have tested with the .ToString() method here and worked fine. Maybe the value on the variable doesn’t match with the excel file that you’re reading.

Regards,

5

Thanks Lucas. I have tried manually and it worked fine and the value on the variable matches the Excel file…

6

It’s possible to you share your workflow with us to check what’s wrong?

7

Apologies for the delay. Please find attached.
Thank you for your help.
Rebecca
Sheet Name Test.zip (17.5 KB)

8

Does anyone have any suggestions please?

Thanks in advance,

Rebecca

9

Hi there @Rebecca1,
Looking over your workflow, I feel like it would be easier to simply read in the Excel file and perform your searching/data extraction via the output DataTable.

For example, you can read all data, find the row corresponding with the provided DBName and retrieve the relevant Country and Category, before moving onto your subsequent component.

This has a significant edge in reliability, as it is not subject to UI automation failures, as well as an advantage in speed.

Regarding your variable for SheetName, it may relate to numerous factors, such as leading/trailing whitespace, capitalisation, etc…

Would you be able to log the specific value to the output pane, copy that result and post it here?

Thanks,
Josh

10

Hello Rebecca!

I’m sorry for my delay. I just forgot about it :frowning:

Anyway, here’s my solution for your problem: Sheet Name Test_2.0.zip (20.9 KB)

Hope you understand what I’ve done. Any doubts, please let me know and I’ll explain to you.

I’m sorry again :sweat_smile:

Regards,

1 Like
11

Thanks so much for your help, very much appreciated. This method looks much more logical, I will give it a go now.
Thanks again!

1 Like