How to read ranges of books

Please tell me, I have a list (ListFileBW2) that contains the names of the Excel files, the variable is called “file_nameBW2”. I need to read the ranges in turn of all books with the file name “file_nameBW2”. I created a loop for each to read the ranges and then concatenate the ranges, but the robot shows an error.
What should I write in the activity “read range”?



error2
I really need to read all the books on the list(ListFileBW2)

Where is the sheet name ??
Item.substring(0…)
Is that your syntax match with your excel sheet name

Is your list variable consists of whole excel path like
“C:\test\sample.xlsx”

It turns out the name of the sheet will consist of the value file “file_nameBW2"without “.xls”. I tried to highlight this value to the point “item.substring(0,item.IndexOf(”.”)) ".This is not true?

if your list variable like this (“C:\test\sample.xlsx”,“D:\test\sample.xlsx”)
And you sheet variable file_nameBW2 = “sheet1”(your excel sheet name and i am assuming that all the excel file has same sheet name)

Pass the variable in. Read range
File name as item (for each iterative variable)
Sheet name as file_nameBW2
Leave range tab as empy

Thanks,
robin

no, each sheet has a different name, just can’t pass the variable in the “Read Range”

Then create another list variable
ListFileBW2=(“C:\test\sample.xlsx”,“D:\test\sample.xlsx”,D:\test\sample.xlsx")
List_variaable2= (“sheet1”,sheet2",“sheet3”)
C:\test\sample.xlsx contains sheet1
D:\test\sample.xlsx contains sheet2 goes on

Now inside the for each loop
Create a counter variable (counter =0)
Pass file name as item(for each iterative variable)
Sheetname as List_variaable2(counter)
Leave range tab as empty
Increment the counter (counter=counter+1)
Now it will read excel file dynamically

thanks
Robin

Hi,

The following helps you.

img20211227-1

System.IO.Path.GetFileNameWithoutExtension(item)

Regards,

@Yoichi , Hello!


still gives an error

Hi,

Can you share content of item variable and actual sheet name in the workbook, when error occurs?

Regards,

I have a list ListFileBW2, it contains the names of the


workbooks with the title as an example: “1BW2”, “2BW2” and so on. format:“xls”
variable file_nameBW2=count.ToString+“BW2.xls”

I need to open all files one by one.
The sheet names contain the title “1BW2”,“2BW2” and so on
it turns out I need to open a file with a name “file_nameBW2” but without “.xls”

Hi,

GetFileNameWithoutExtension methods returns just filename without extensions and it seems good logically.

There might be some extra space in your sheet name. Can you share your excel file, if possible?

Regards,

1BW2.xlsx (9.5 KB)

Hi,

Thank you for sharing your workbook. I just wrote simple sample workflow as the following.
Please check your content of item variable and actual sheet name in the workbook, when error occurs.

Sample20211227-a1.zip (9.8 KB)

Regards.

thank you! It only works if you use read range in Excel Application Scope

Thank you!