Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Is it possible to use Alteryx to delete sheets from an Excel file?

mix_pix
10 - Fireball

Hi,

 

Here's my situation:

 

  • 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. :-)

 

Thanks,

 

-Mike

9 REPLIES 9
dataMack
12 - Quasar

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.

 

SNAG-0026.png

mix_pix
10 - Fireball

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. :-)

Bhavika
8 - Asteroid

@mix_pix 

Hi there, i am working on exact similar thing. Could you please assist if you found calling a VB script to delete data from an excel file and re-saving it.

 

Thanks.

 

cmcclellan
13 - Pulsar

What is the pivot table used for - just to view or do the users actually interact (drill/filter/etc)  with it ?

Bhavika
8 - Asteroid

@cmcclellan 

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 .

 

Could you please help me with this?

 

Thanks

cmcclellan
13 - Pulsar

Check your output tool, you can adjust the settings to drop the sheet and create it again.  Test how this affects the pivot table as well.

Bhavika
8 - Asteroid

@cmcclellan 

 

The Source Data tab has certain fixed formatting which (Bold Headers, Numeric fields as specific excel number format  etc). So, i used the Append to existing sheet option.

mix_pix
10 - Fireball

Hi,

 

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.

pivot 1.png

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.

 

pivot 2.png

Jeyez
6 - Meteoroid

Hi,

 

I get the set up of your destination file and source file:

 

  • source file: \\tableaunetapp.seagate.com\tableau_prod\SMC\Staging\CommonSMCBIdata\Misc\with data and pivot.xlsx
  • Destination file: \\tableaunetapp.seagate.com\tableau_prod\SMC\Staging\CommonSMCBIdata\Misc\with pivot only.xlsx

 

But could you explain a little further on what are these cvs files doing here:

  • \\tableaunetapp.seagate.com\tableau_prod\SMC\Staging\CommonSMCBIdata\Misc\excel data tab deletion details.csv
  • Output: \\tableaunetapp.seagate.com\tableau_prod\SMC\Staging\CommonSMCBIdata\Misc\dummy.csv
  • Command: D:\Program Files\Microsoft Office\Office16\EXCEL.EXE
  • Command Arguments: \\tableaunetapp.seagate.com\tableau_prod\SMC\Staging\CommonSMCBIdata\Misc\_delete data tab v2.xlsm

Thanks!

Labels