cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bjorn
Level: Powered On

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
Bjorn
Level: Powered On

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

2 REPLIES 2
Community Support Team
Community Support Team

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.
Bjorn
Level: Powered On

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

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

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 Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,780)