Creating Slicers in Excel with VBScript (not VBA)

Hello,

First I want to say that I am using VBS instead of VBA for multiple purposes:
- I needed to make a script that works on multiple computers without having to either install an add-in, or add macros to the PERSONAL.XLSB file
- I needed to make this work both with the SAP ERP and Excel (I can do it straight from VBA, see reason above why not)
- I used VBS under HTA (HTML for Applications) to have a portable application that works
- I needed a nice GUI to it, dynamic lists and checkboxes, that's why the HTA (I know most if not all of these can be made in UserForms)

I've made a code that I'm not going to post in its entirety since it's not needed. Lemme describe what it does:
- button 1: based on the info from the HTA GUI interface, I extract a table from SAP to Excel - no problem here
- button 2: adds a Pivot table based on the data extracted by button 1, inserts Filters and some Calculated columns - no problem here

Problem:
- I'd like button 2 to also insert a couple of slicers to the Pivot table and can't seem to get my head around this

What I did so far:
- I recorded a macro inserting Slicers in Excel and tried to adapt it to VBS but couldn't
- I am assuming I have to set a slicer cache but the syntax seems different than in Excel and all my tries either led to errors or to the code doing nothing

Here's the part of the code I used to make the Pivot table and add some fields (and please see the attached sample file for data sheet + Pivot table):

Please Login or Register  to view this content.

Any help is appreciated, thank you for your time!

I finally found the answer myself. Maybe I had posted too little code above, so nobody could help.

Anyway, here's the way you create Excel pivot table slicers from VBS:

Please Login or Register  to view this content.