This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have an Excel file that has two tabs - a 'Data' tab and a 'Pivot' tab
The 'Pivot' tab linked to the 'Data' tab and is set to auto-refresh upon open
I use Alteryx to update the 'Data' tab and re-save the file to a network directory
I use the Email tool to email the Excel file as an attachment to a set of users
This process works very well, but I am now exceeding the 10MB limit of the Email tool, which means that while the Excel file is updated, Alteryx won't email the file as an attachment.
I noticed that if I delete the 'Data' tab (after the 'Pivot' tab has been updated), I substantially reduce the size of the Excel file (since Excel places the data into a pivot cache, which appears to be a very compressed version of the original data). Is there any way for Alteryx to do the following:
Update the 'Data' tab as it already does
Refresh the 'Pivot' tab
Delete the 'Data' tab and then re-save the file
Is this something that can be accomplished via the Run Command tool? I hope there's a way to accomplish this. :-)
The 'doubling size' of data in excel has been an issue as long as pivot tables have been around. Luckily the fix is easy and you don't have to modify your Alteryx workflow.
In Excel, just click on the pivot table then go to Options and the 'Data' tab. Uncheck the firts 2 options and check the 3rd. This will prevent Excel from duplicating all your data into the pivot cache and will force the file to refresh the pivot directly from your data tab every time the excel file is opened. Nice and easy.
Thanks for the reply Jason. Unfortunately changing those settings only shaves a little size off of the Excel file. So I've been working w/ my team to call a VB routine from Alteryx that opens an Excel file, deletes the data tab and then re-saves it. If it works I'll post the solution here. :-)
So, the users do all the traditional things to the excel pivot (drill down/ filtering/double click on cell and it redirects to the underlying data)
As per what i have read in the community, the best way is to Output data from Alteryx to the source data tab in the excel and refresh the pivot table in excel on opening. Now, the challenge i face is that i manually delete the historic data in the Source data tab before running my workflow and use the output tool (Append to existing option) to replace the Source data tab with new data. Now, i want that Alteryx deletes the historic data at the beginning of the workflow and the end product of my workflow is outputting the new data to the Source Data tab .
We did end up making this work. I switched jobs a year ago and so no longer have access to some of the files we used (specifically the Excel VBA module), but here's what I do have:
We would output the data from the workflow to an Excel file that had multiple tabs - one with the raw data and one with a formatted pivot table.
We would then call a macro that would refresh the pivot and remove the raw data tab.
The macro had three parameters - the source file, the destination file and the tab name you want deleted. The idea is that it would open the source file, execute the VBA to refresh the pivot, delete the data tab and then save as the destination file. Like I said, I don't have the actual VBA that performs these steps, but I recall it being only a few lines of code...a very lightweight file. I'm attaching the macro workflow so that you can see the tool settings. Hope this helps.