Auto Refresh Excel Pivot Tables

Question

I am reaching out to see if anyone might have some suggestions on how to go about forcing an excel file to refresh so that the pivot tables pick up the new data.

We have referenced the discussion here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Auto-refresh-pivot-after-getting-the-output/td-p/566087 and have implemented this but still does not correct things for the end user...

Background: We have a workflow that used to be massive due to the sheer number of calculations that were being done in the report in excel. We have changes this to be an excel pivot table. We are using Alteryx to run the background data that updates daily and then emails the file to our end users. We have users using Macbook Pro and users using PC as well. We have been getting users report that they are unable to hit the update pivot and so we created a run command that would take case of opening and saving the file to force it to refresh before sending out to the end user. As we created this on a local machine - the workflow works and updates the file, however when we schedule it on our virtual machine that is used for automation of our files it creates an error as that machine does not have excel on it and so the automated process stops and does not continue to run.

Question: Is there another run command / tools in Alteryx that would force the excel file to refresh before sending to the end user that doesn't require us to have excel on the virtual machine? Looking forward to anyone that might have some input here

csmith11 · Answer

Also would configuring the Excel file differently help?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Auto-refresh-pivot-after-getting-the-output/td-p/566087

Right Click on the Pivot-Table -> Options -> Data -> "Refresh data when opening the file"

csmith11 · Answer

You might be able to use openpyxl.

You'll be able use the python tool to accomplish this.

I can't tell you exactly how to deploy it but here is a good starting point for the openpyxl documentation.

https://openpyxl.readthedocs.io/en/stable/pivot.html#:~:text=openpyxl%20provides%20read%2Dsupport%20for,be%20preserved%20in%20existing%20files.&text=However%2C%20it%20should%20be%20possible,they%20should%20update%20automatically%20settings.

mceleavey · Answer

Hi @jnans ,

The first thing to do would obviously to stop reporting in Excel.

Second, Stop reporting in Excel.

If this can't be done yet, try outputting to the range of cells from which the pivot table reads.

Alternatively, build the pivot table in the table tool and render as an Excel output.

Then...stop reporting in Excel.

M.