cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Extract the value of the same cells in various sheets in an excel file

Hi all,

a client of mine issues invoices through excel. Instead of creating one file per invoice, it has created various sheets in one single file.

The structure of the invoice is the same, and luckily the values i need are all on the same cells in each sheet. These values are also on the same column.

There are 511 sheets in this file.

I was thinking to set up something like this:

Cattura.PNG

 

 

And, since one cannot choose directly the cell, but must pass through a table, put a dynamic name in "list rows present in a table":

Cattura2.PNG

I would then like to extract the 3/4 cells of interests that fall between the range c9:c40 and put them in another specific excel file, that must be populated to be then uploaded to our ERP.

At each run, the variable will be increased by 1 so that we can move to the next sheet, till the sheet n°511.

 

Unfortunately, it does not seem to work… In fact, i don't know how to choose the specific cells in the "list rows present in a table" action..

 

Did you ever managed something similar?

 

Thank you in advance!

 

Vittorio

4 REPLIES 4
Highlighted
Super User II
Super User II

Re: Extract the value of the same cells in various sheets in an excel file

The Excel connector has many Known issues and limitations and therefore is not recommended for heavy lifting workloads such as this. You're likely hitting some of those issues or limitations with your current Flow due to the size of the Excel file and the 500+ sheets. For workloads of this scale, it is recommended to migrate to SharePoint lists. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Frequent Visitor

Re: Extract the value of the same cells in various sheets in an excel file

hi @Brad_Groux ,

thank you for your reply. 

 

I am aware of the limitation, but this is just an exceptional occurrence, and the idea of using flow came to me just to avoid filling manually an online tax form with  the data of all the invoices. Besides, I cannot tell the client to use SharePoint because the documents are from last year. 

 

Were the file smaller and with (let's say) fewer sheets, would that be a technically correct solution? or can you suggest other ways?

 

Thanks a lot again!

 

br,

 

vittorio

Highlighted
Super User II
Super User II

Re: Extract the value of the same cells in various sheets in an excel file

There is no real way of knowing when the size of the file would come under the thresholds for the connector, this is the very reason that Microsoft has deprecated many, many actions, triggers, and definitions from the Excel connector over time - it becomes a nightmare to support and troubleshoot.

You may be able to use the PowerShell Excel Module to extract the sheets to individual Excel files (example). 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Frequent Visitor

Re: Extract the value of the same cells in various sheets in an excel file

Hi @Brad_Groux , thank you very much for the follow-up. I have decided to give it nonetheless a try, using as output destination a sharepoint list:

So basically i'll use a "do until" to make it recoursive:

000.PNG

 

 

Problem is, i have a issue using the corect syntax for the "table". Indeed, there are no tables, as it is an external excel file. I have tried to use the following syntax, that basically gives (in the current sheet, which is dynamicly identified) the range of a single column that starts on the first relevant cell, and finishes on the last relevant cell, all in the same column:

001.PNG

but it returns this error:

The parameter 'table' has an invalid value 'Foglio1!C9:Foglio1!C40'.

 

Do you have an idea about the correct syntax that should be used to identify the correct Row in the given variable sheet?

 

Thank you and best regards,

 

Vittorio

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

Join the new Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Users online (7,501)