Get data from multiple Excel file having multiple Worksheets

[Sab]

Hello,

I know how to get data from multiple workbooks and also from multiple worksheets WITHIN ONE workbook. But I cannot do that when there are multiple excel files with multiple worksheets...

I have created parameters and everything looks OK

But when I expand ALL_Workbooks it gets data from first file only AND reads the first file 3 times. So instead of reading each file 1 time, it reads first file 3 times!!!

Any help would be greatly appreciated!

Thanks,

Sab

[Sab · Accepted solution]

Thank you for your replies. 

The problem is a bit more complicated than in the example from the video because sheets contain Pivoted data with dates in column headers, and every file/sheet has different Dates (cloumn headers), therefore I cannot use the first row of any of the sheets as header. 

Still trying to figure out a solution with Functions and parameters...

😞 😞 😞

[ImkeF]

Could it be that the parameter in your function is ignored?

[Sab · Accepted solution]

Thank you for your replies. 

The problem is a bit more complicated than in the example from the video because sheets contain Pivoted data with dates in column headers, and every file/sheet has different Dates (cloumn headers), therefore I cannot use the first row of any of the sheets as header. 

Still trying to figure out a solution with Functions and parameters...

😞 😞 😞

[Sab · Accepted solution]

Hi,

I solved this problem by creating two parameters, one that holds file name and the other one that holds sheetname. Lots of effort on this one but it was worth because I learned some interesting stuff.

thanks again

Sab

[Ashish_Mathur]

Hi,

Follow the steps outlined in this video.