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

Update References inside an Excel File

Hi there,

 

I am one step further (https://powerusers.microsoft.com/t5/Building-Flows/Excel-workbook-parsing-error-Unable-to-parse-the-...) to making my Flow work.

 

Here's the idea:

 

  • I created a small table on the sheet, that I fill via the "Excel Insert Row" action
  • 3 cells of the form (for instance D12) reference the values of the table
  • I finally want to convert the Excel file to a PDF File and save it

The form:

 

 Formular.jpg

 

 

 

The current Flow:

 

tovorlage.JPG

 

The problem: 

 

All the steps are working fine, but when I export the file in the "convert excel to pdf" and then "create pdf file" the references are not updated. The small table contains the information I want, but the cells that should update to reflect those changes (for instance D12) are empty.

 

Is there a way to force Excel to update the sheet?

 

Thanks!

5 REPLIES 5
Community Support Team
Community Support Team

Re: Update References inside an Excel File

Hi @TimBourguignon,

 

Could you share more details on your scenario? I assume that you have an Excel table and you will insert value into the table. Then you are trying to get the value from the table then save it into the form.

 

About the form, I guess that there must be special formats in it. Am I right? Have you formatted it as a table?

 

In the Insert row action, could you get the table of the form? Could you show me the detailed configuration?

 

 

Best regards,

Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Update References inside an Excel File

Hi Mabel,

 

thanks for the answer.

 

As you can see on the first screenshot below, the form (on the left hand side) is fairly complex and cannot be easily transformed into an Excel Table without altering the visual structure. So I thought I could create a very small table (on the right hand side), - that I successfully filled with data via step 4 of the flow - and reference this table in the corresponding cells of the Form:

 

 

  • Cell E10 and E11 reference the "Date" column of the table on the right.
  • Cell D12 references the "Reason" column 

 

In order to take the screenshot, I opened the Excel table manually and then the references are correctly processed. Thus the "23.08.2018" and "Bootcamp Mittagessen Test" being present in the form.

 

tempsnip.jpg

 

This second screenshot below is the PDF I sucessfully produced at the end of the flow. As you can see, the table on the right is correctly filled, but the references on the left are not refreshed.

 

This is the problem I am facing right now. The references only seem to be processed when I open the workbook and not when I "just" modify the data in the background with a flow and then export it as a PDF.

 

failformfilling.jpg

 

Does it makes more sense?

 

Here's the PDF creation:

 

pdfexport.jpg

 

I don't think it has to do with formatting of the cells since when I open the workbook manually, all the references are correctly processed. I added a delay before creating the PDF but it doesn't seem to have any effect...

Super User
Super User

Re: Update References inside an Excel File

Hi @TimBourguignon 

 

I would be very interested to know if you managed to solve this issue? 

We have a Flow internally that is taking a response from a Microsoft Form submission and writing this to an Excel file stored in OneDrive. The Excel file is structured in such a way so that when it is saved, the formulas and calculations populate a "report" worksheet. We are then Converting this Excel file to a PDF using the OneDrive actions and saving it to another location. 

Nine times out of ten this process works flawlessly and we end up with a PDF output containing all the values from the Form in a nicely formatted report. Ocassionaly though, we are seeing issues where certain values are not being populated correctly and don't show up in the output, almost identical to what you are experiencing. We have also tried adding a 5 minute delay after the step that writes to Excel but this didn't appear to make any difference.


Did I answer your question? Mark my post as a solution!

Proud to be a Flownaut!


DaveNicholls
Level: Powered On

Re: Update References inside an Excel File

I have exactly the same issue as described above. It is almost as if the excel book isn't calculating the formulas referencing the table when publishing to PDF. Sometimes it works, sometimes not. Did you find any solutions?

I notice the Convert File to PDF action is still in Preview mode, whether the volatility of this could be to do with that?

Justin2
Level: Powered On

Re: Update References inside an Excel File

Glad to hear that I'm not the only one having this problem.

I think it is definately related to some triggers firing when Excel is being opened (vs. convert to PDF) which is driving whether or not the the cells get their references updating properly.

 

The workaround I have found is to make 2 separate flows (I know this is not ideal). 

But this seems to guarentee that the pdf generated matches the excel values.

Flow1 = update the excel file

Flow2 = create a pdf of the excel file + attach to email etc.

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 57 members 4,809 guests
Please welcome our newest community members: