Update References inside an Excel File

[Anonymous]

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:

 

The current Flow:

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!

[v-yamao-msft]

Hi @Anonymous,

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

[Anonymous]

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.

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.

Does it makes more sense?

Here's the PDF creation:

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...

[LeeHarris]

Hi @Anonymous 

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.

[DaveNicholls]

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]

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.

[BenDonahue]

In addition to all the other bugs and glitches that Excel offers, compounded with the bugs and glitches Flow brings to the table, here is yet another.

In a professional workflow that reflects on us and effects our reputation and standing with our colleagues, company and superiors, it is a good thing we are only dealing with data, and not something that relies on consistency and accuracy I guess.

I am having this issue, also.

Now, I just have to figure out how to trigger one workflow to start at the end of another workflow, because that makes sense.