How to get information from excel if I do not know name of sheet and how many columns

[Aggi (Agata)]

Dear experts,

please provide some idea for next situation:
I will download excel files. I do not know name of files, name of sheet and how many columns will be in those files. I should check if files contain some key word in any columns inside.

Thank you for support and ideas!

[MAHESH1 (MAHESHKUMAR JV)]

@Aggi

Refer this

Read range Excel sheet index

Hi, I would like to use the readrange activity to read range from an excel file. [image] Is there any way to refer to the index of the sheet instead of the name of the sheet? I have to read range from the first sheet of the excel file, but the name of the sheet always refers to the user’s name, so that is always different. Could you please help me?

Regards,
Mahesh

[arivu96 (Arivazhagan A)]

Hi @Aggi,

use the below code to get the download path and store it into string variable(strpath)
Environment.GetFolderPath(Environment.SpecialFolder.UserProfile)+"\Downloads" **or** Environment.ExpandEnvironmentVariables("%USERPROFILE%\Downloads")

strXLFilePath=Directory.GetFiles(folderlocation, “*.xlsx”)(0)

use Excel Application scope to read the sheet. (Refer the below path how to get the sheet names)

How to get the Sheet name from Excel

Dear all: I have a solution for y’all. First, install this package. ExcelActivities.1.0.18.nupkg (9.5 KB) Second: open this xaml and set up the path to an excel workbook with more than one sheet. Readsheetname.xaml (7.7 KB) Please, please comment your findings on this.

Read range Excel sheet index

Hello there, Yes you can. Use excel application scope activity , create output variable from the property called workbook(eg:wb) To see the sheet name just use write line and pass like this wb.GetSheets(0). Then use Read range and in the sheet pass like this wb.GetSheets(0). PS: make sure to use that outside the excel scope(means you need to add one more excel scope and use read range there)

Then use Read Range to get the data from the excel.

Regards,
Arivu

[Rajiv_Mulchandani (Rajiv)]

Hi,

Can some one please explain what does (0) at the end of the above Expression mean?

I tried the code without (0) and it would not let me assign it to a String variable since it was Expecting a String array

i used (0) then and it resolved the issue.

Your Explanation on this will be very valuable.

Thanks in Advance!

Regards,
Rajiv

[arivu96 (Arivazhagan A)]

Hi @Rajiv_Mulchandani,

@arivu96

strXLFilePath=Directory.GetFiles(folderlocation, “*.xlsx”)(0)

Directory.GetFiles will return the value of array.

Here (0)-> it’s means first value of the array value.

@arivu96

wb.GetSheets(0).

(0)-> it means the first sheet.

Regards, Arivu

[Rajiv_Mulchandani (Rajiv)]

Hi Arivu,

Thankyou for your Explanation!

That was very helpful. It Resolved my query and now i understand it just perfect.

Regards
Rajiv