Refresh excel pivots without opening file using Python Tool

[Nikita-Puniani]

Hi,

Is there a way I can refresh excel pivots once the data tab is loaded with data via alteryx using Python tool?

[Luke_C]

Hi @Nikita-Puniani 

Just want to understand the use case here, is there a reason you can't use the pivot table options to refresh the data when the file is opened?

[Nikita-Puniani]

Hi @Luke_C  Thanks for the question.

The excel file has many pivot tabs that are directly connected to the database. The user wants the data to be refreshed in the excel every time a stored proc is run. There are 100+ excel files and it would be cumbersome to open each file, refresh the connections and then send it in email.  Any thoughts? 

[Luke_C]

I can't speak to a python solution unfortunately. I would think that having the data upload when the user opens the file would be acceptable unless it takes a significant amount of time.

[Nikita-Puniani]

Hi @Luke_C 

Sorry for the delay in response.

The refresh needs to happen programtically because there is data dependency on these files downstream.

At the end of the process all the excel spreadsheets updated for that day are then emailed to the business users.

I have come up with a  windows powershell script that works on my local alteryx desktop. However I am not sure how it will work on alteryx sever. Trying to figure it out

[Luke_C]

Hi @Nikita-Puniani 

That makes sense, I assume you're using the workflow events menu or run command tool? They should work on server but you may need to work with the admin to ensure the correct permissions are in place. 

[Nikita-Puniani]

Yes I am using the run command tool. But now I am trying to dynamically pass the input parameters of the filepath to the powershell shell. My script has been updated to work with accept parameter arguments and I tested it out on command prompt and it works fine but I am not sure how to configure the run command to accept the params at run time in alteryx server Started separate post of this issue. https://community.alteryx.com/t5/Alteryx-Server-Discussions/Passing-command-arguments-to-Run-command... Any feedback is appreciated

[raychase]

I use DCOM objects, called via PowerShell, to achieve such results.