Extract data from multiple Excel Files in same folder

Hi @JulianRPAdev 

You may try below steps for this use case

1. Loop through each file in folder and assign the output to a dictionary variable.
2. Inside the loop use excel open and specify the path. The file name will be the dictionary variable with key as “name”. The extension will be .xls or xlsx or you can use the dictionary variable with the key “extension”
3. You can perform the excel operations here like get cell etc. To get the number of row (hope this is what you wanted” you can use “Get number of rows” command under excel which will give you total row in the excel which has data.
4. Close the excel

Keep in mind to have same session in all the excel commands which will be inside the loop.

Hope this helps!

Hi @Abhay Naik

Thank you very much for the fast answer!

I now 1.: loop for each file in folder and assign the file name + extension into a new Dictionary variable which One key named “name” and empty Value. 2.: inside the loop I try to open the first excel file by adding the Desktop file path to the folder + ‘/’ + the dictionary variable, but does not work..

How should the Desktop file path in “Excel advanced Open”-action look like? Example: C:\Users\USER1\Desktop\folderwithfiles

Seems like I do not fully understand how to use the dictionary variable..

Afterwards I will perform the operation “go to cell xy” and pull the string from there into a variable in order to use this information to query something in SAP.

Hi @JulianRPAdev

Please find the Screenshots below.

Step 1.

Step 2.
Enter the folder path then after “\” click on select variable button which is highlighted below. Select the dictionary variable which will ask you to enter the key where you can put “name” as the value.

(C:\Users\Abhay\Desktop\Temp\$filesInFolder{name}$.$filesInFolder{extension}$) → for example the file path in excel will look somewhat like this

Step 3.
 

Step 4.

Hope this will help!

Dear @Abhay Naik  thanks for the single steps.

Steps 1-3 worked perfectly and I just wanted to ask for one more advise as I will continue differently in my bot.

How can I make sure, that AA360 gets the single value of cell XY10 (in your case) just for the first file in the folder and then puts this value into a variable where it is afterwards used to be inserted into SAP?

After I copied something from SAP I want to proceed with the 2nd excel file in the folder until the same process is done for all files.

I have no issue with connecting to SAP, works perfectly fine. I just understand that in the current use-case, we open all excel-files in the folder and get the value of the cell XY10 for each file.

Case is solved - it works like it should with your solution