cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nightfall
Helper I
Helper I

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

1 ACCEPTED SOLUTION

Accepted Solutions
Brad_Groux
Super User II
Super User II

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!

View solution in original post

4 REPLIES 4
Brad_Groux
Super User II
Super User II

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!

View solution in original post

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

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!

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
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (60,485)