Extract data from specific cells from multiple sheets in the same excel file

[Page0727]

I am able get all sheets out of an excel file and combine them but does anybody know how to extract data from specific cells across multiple sheets in the same excel file. The data contained in the sheets is not the same apart from these two specific cells in the sheets, so I cant combine them and then extract the columns.

Thanks

[bruno29a]

Hi @Page0727 , in the Excel Reader, you can specify from which cell to which cell you want to read the data in the Sheet area:

So, you can set these values when you read all the sheets, and it will read these same specific cells from all the sheets.

[Page0727]

Hi Bruno,

I have tried this and should have noted this in the post. Sorry about that. I have a sheet reader and then a Table Row to Variable Loop Start, with the Excel reader pointing to the path variable and selecting cell B 1 to 1. Finally I have a Loop end. When I run this it just returns the cell from the very first sheet over and over again.

[bruno29a]

Hi @Page0727 , can you show us how your Excel Reader is configured?

It sounds like you are not reading the sheets dynamically within the Loop.

You need to make sure that you use these settings:

1. In the Settings tab, choose the “Select sheet with name” option (don’t mind what’s in the drop down, we will override it in the next step)
2. In the Flow Variables tab, select your variable that contains the sheet name from the drop down from the “sheet_name”. This variable will dynamically change value within your loop

After all this, you should see this message at the bottom of the Excel Reader on the Settings tab:

[mlauber71]

@bruno29a, @Page0727 not sure if this has been fixed but it could make sense to force the use of sheet name via variable:

[bug or no bug ?] Excel Reader preconfigured work sheet selection options

@Geo what you could do is force the use of sheet_selection = “NAME” thru a Flow Variable in case of doubt. Maybe not the most elegant way but shout provide an additional layer of security. [image] And indeed under certain circumstances it seems to revert to the default value. Not able to reproduce it yet in a constant way. But might be worth looking into, @laaaarsi. [image]

[Page0727]

This worked changing from ‘Select First Sheet with Data’ to ‘Select Sheet with name’.

Thanks!

[bruno29a]

Hi @mlauber71

@mlauber71

it could make sense to force the use of sheet name via variable:

Yes, it is what I suggested to do

[bruno29a]

Thanks for confirming @Page0727 . Happy to help

[Page0727]

Thank you also for the suggestion. I really appreciated your input.

[mlauber71]

@bruno29a

Yes, it is what I suggested to do

Yes the addition was that sometimes there seems to be a bug (AP-18133) where just setting a dummy name and giving a name via Flow Variable is not enough because of a yet unfixed bug that would reset or ignore these setting under certain conditions. So the idea was to also force the “sheet_selection” thru a variable which would otherwise not be necessary.

Thank you for your very detailed and well illustrated solutions.

[bruno29a]

I see @mlauber71 , I was not aware of this bug, thank you for sharing.