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

OneDrive Convert Excel to PDF - Calculated Cells are Blank

I've built a Flow that converts generated Excel into PDF using the OneDrive 'Convert File' action. Unfortunately it only renders cells with values, it ignores formulas, cell references, and table formulas. I realize there are are third-party conversion actions from Plumsail and Muhimbi, but I'd like to use Microsoft's because it generates the best looking PDFs, and because it's also free. We actually use Plumsail for a bunch of other things and they're great at what they do, but their PDF conversion doesn't look as good. But they do manage to render formulas, calculated cells, and table formulas, so I know it's possible. Why can't OneDrive's Convert File do this as well?

 

Here's an Excel file and the corresponding OneDrive converted PDF. You can see all 4 table totals are blank, as is the yellow cell which has a =2+2 formula.

 

onedrive-pdf.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resolver I
Resolver I

Re: OneDrive Convert Excel to PDF - Calculated Cells are Blank

Hi Mabel,

 

Thanks for the reply. I finally solved my issue, posting this in case it helps anybody else.

 

I probably should have mentioned that my Excel files are dynamically generated using Plumsail Documents. There could be 1 or more rows in a given table, and my totals are references to these dynamic tables. My Excel template looks like this (the {{field}} values are replaced at run time):

 

2019-04-09_11-40-24.png

 

 

The Excel template has auto-calculations set to On but technically the files are never 'opened', so the auto-calculate never gets the chance to recalculate the new table totals. The OneDrive PDF converter sees empty cells and does nothing with them. I used 7zip to open the Excel and edit the xml directly. If you delete the value nodes <v> it forces Excel to recalculate: <c r="G7" s="19"><f>SUM(Table1[NOTIONAL])</f><v></v></c>

 

But this didn't work either, Excel doesn't open and repopulate the calculated values. But what did work was using Flow to try to 'Open' the Excel file and add something to it, see below. There are likely other ways to do this, but here's what worked for me.

 

I have a hidden worksheet with a table named PdfExtraTable that has one column. I use the 'Add a row to a table' action to add a value to this hidden table. I then use a 30 second delay before converting the Excel to PDF. I've tested it five times now and all my converted PDFs have total rows with the correct values! I tried without the delay and it didn't work. I also tried the 'Get rows' action without updating itand that didn't work.

 

 

 

2019-04-09_11-38-18.png

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Re: OneDrive Convert Excel to PDF - Calculated Cells are Blank

Hi @Bjorn ,

 

Could you share a screenshot of your flow? How is it created?

 

According to your description, I have created an Excel table with calculated cells and formulas.

 

Then start the flow likes below.

 

When the file is converted to pdf, I can see data within calculated cells.

 

Screenshots for your reference.

1.PNG2.PNG

 

Best regards,

Mabel

 

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.
Highlighted
Resolver I
Resolver I

Re: OneDrive Convert Excel to PDF - Calculated Cells are Blank

Hi Mabel,

 

Thanks for the reply. I finally solved my issue, posting this in case it helps anybody else.

 

I probably should have mentioned that my Excel files are dynamically generated using Plumsail Documents. There could be 1 or more rows in a given table, and my totals are references to these dynamic tables. My Excel template looks like this (the {{field}} values are replaced at run time):

 

2019-04-09_11-40-24.png

 

 

The Excel template has auto-calculations set to On but technically the files are never 'opened', so the auto-calculate never gets the chance to recalculate the new table totals. The OneDrive PDF converter sees empty cells and does nothing with them. I used 7zip to open the Excel and edit the xml directly. If you delete the value nodes <v> it forces Excel to recalculate: <c r="G7" s="19"><f>SUM(Table1[NOTIONAL])</f><v></v></c>

 

But this didn't work either, Excel doesn't open and repopulate the calculated values. But what did work was using Flow to try to 'Open' the Excel file and add something to it, see below. There are likely other ways to do this, but here's what worked for me.

 

I have a hidden worksheet with a table named PdfExtraTable that has one column. I use the 'Add a row to a table' action to add a value to this hidden table. I then use a 30 second delay before converting the Excel to PDF. I've tested it five times now and all my converted PDFs have total rows with the correct values! I tried without the delay and it didn't work. I also tried the 'Get rows' action without updating itand that didn't work.

 

 

 

2019-04-09_11-38-18.png

View solution in original post

Highlighted
Helper II
Helper II

Re: OneDrive Convert Excel to PDF - Calculated Cells are Blank

@Bjorn Thank you, I was generating some PDF and the graphs did not updated, but you help me to solved it! 

PD: In mi case I put 45 seg instead 30 and then work 😉

Helpful resources

Announcements
Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

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

Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Top Solution Authors
Users online (7,344)