Connection to Microsoft Excel - changing Excel file names

[Jurgen]

Hi experts,

I am creating a flow with a connection to an Excel file. In this Excel file a Table is added in an seperate tab.
The excel file itself changes names every time.

When connection to Excel (online for business), I get the same errors over and over .

If I uses the Name of the file in the flow, I get following error:

Headers

Key

Value

Pragma

no-cache

x-ms-request-id

8ba77442-242e-4ac9-91b9-a87a0af32eed

Strict-Transport-Security

max-age=31536000; includeSubDomains

X-Content-Type-Options

nosniff

X-Frame-Options

DENY

Timing-Allow-Origin

*

x-ms-apihub-cached-response

false

Cache-Control

no-store, no-cache

Date

Fri, 26 Apr 2019 14:20:10 GMT

Content-Length

301

Content-Type

application/json

Expires

-1

Body

{

  "status": 400,

  "source": "excelonline-we.azconn-we.p.azurewebsites.net"

}

  "message": "The expression \"drives('b!iSMBRNsm4kmBUHlgfXN_iADn6_WCZeBCgED7z_A5aOYeKt21M2woRo_FgRzLJ9qx')/items/Rework MD template 2504.xlsx\" is not valid.\r\nclientRequestId: 8ba77442-242e-4ac9-91b9-a87a0af32eed",

When I used file content instead of name , I got following error:

InvalidTemplate. Unable to process template language expressions in action 'Get_worksheets_2' inputs at line '1' and column '2535': 'The template language function 'encodeURIComponent' was invoked with invalid parameters. The parameter at index '0' cannot be converted to URI component.'.

If I try to link it to a fixed Excel file, the flow runs perfectly. However each flow that is being trigged is done for an Excel file that changes names everytime.

Can someone help me out?

br Jurgen

[Brad_Groux]

The Get File and Get Table functionality within the Excel Online connectors have very different functionality available, as the table definitions have a very limited number of things available to it. When you load the entire file, it actually loads the file within Excel Web Services, much like traditional SharePoint would utilize the Excel services bits running on one of the servers within the SharePoint farm.

This is also why the file lockout limitations for Excel Online connectors are 6-minutes and 12-minutes in length, which is also why Microsoft Flow experts recommend staying away from the Excel connectors when possible. What is likely happening with your documents, is the formulas and data within a specific row likely require full Excel services to compute, that are beyond what is available via the table definitions.

You have a few options to work around this, which would likely be uploading the Excel files to SharePoint lists and/or creating a Flow that starts with a When a file is created (properties only) trigger. Here's a quick mockup of how the second option could look:

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!

[v-zhos-msft]

Hi @Jurgen ,

Could you please share a screenshot of your flow's configuration?

I can't understant your scenario clearly.

I need more details to reproduce your issue on my side.

Best Regards,

Community Support Team _ Zhongys

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jurgen]

Hi,

Flow looks at follows:

Purpose is to read the information out of an Excel file that contains a table.

The Excel file name contains the title of the new products we are launching, so this name changes every time.

When I select 1 specific Excel file the flows works. However I can not use the 1 Excel file as the name of every document is different.

When I select file Name, I get an error:

When I select file content I get following errors:

I hope this make things a bit clear. The biggest struggle in this flow is to get the Excel connector working with a changing Excel file name (each Excel file has a different one and the flow needs to read the excel table of each of them).

Br,
jurgen

[v-zhos-msft]

Hi @Jurgen , 

I have made some tests on my side.

I have used the 'dynamic content' from the above action(Id, identifier,File name...), they all don't work.

So I am afraid that there is no way to achieve your needs in Microsoft Flow currently.

You can only Select an Excel file through File Browse.

Please take a reference of the Docs of excel connector:

https://docs.microsoft.com/en-us/connectors/excelonlinebusiness/#getallworksheets

Best Regards,

Community Support Team _ Zhongys

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.