Anyone working with Excel Slicers and WB?

[stanl]

I have an application that outputs from a db to Excel over a selected date range. The Excel sheet is formatted as a table with offset row colors, totals at bottom, filtered, and gridlines off. But at work they are all excited about slicers, so I am contemplating moving my beginning row for the table down several rows and placing 1 or more slicers at the top of the worksheet (as part of the automation). This would be with Office 2010. Thought I'd ask if anyone has attempted auto-slicing or ran across some vba code that could be converted to WB.

[Deana]

The excel COM server seems to expose an Slicer object. Here is a link to the documentation for that object: http://msdn.microsoft.com/en-us/library/office/ff820991.aspx

A quick Google search found this code sample that might help get you started: http://www.databison.com/slicer-vba-code-create-change-or-modify-a-pivot-table-slicer-using-vba/

[stanl]

The excel COM server seems to expose an Slicer object. Here is a link to the documentation for that object: http://msdn.microsoft.com/en-us/library/office/ff820991.aspx

A quick Google search found this code sample that might help get you started: http://www.databison.com/slicer-vba-code-create-change-or-modify-a-pivot-table-slicer-using-vba/

Thanks, and I apologize for miswording the post. The issue is that Office 2013 permits slicers in data tables as well as Pivots, but 2010 only allows slicers for Pivots. My script outputs data tables (which come predefined as a table style and filters) and have calculated percent fields.

What that means is somehow transforming the data table to a Pivot but keep the original data structure [so that the Pivot does not attempt to total percents]. 

I tried placing my Data export in Cell A15, thus leaving the top of the sheet to place the slicers.

Then I tried

Code (WINBATCH) Select Expand


xlDatabase = 1
oWS = oXL.Activeworkbook.Worksheets("Data")
oWS.Activate()
oRange = oWS.UsedRange
oWS.PivotTableWizard(::SourceType= xlDatabase,SourceData= oRange,TableDestination = oWS.Range("A15"))


But get an error saying Unable to get PivotTableWizard.

I attached a sample file with a macro I also tried to execute [without success].  It should work according to what I've read.

[EDIT]

It gets ugly, a pivot cannot overlap a table, so have to create a pivot cache which makes a new sheet, then transform the data. Then worry about slicers. Need a plan-B

[Deana]

Sorry Stan, this is outside the scope of my Excel knowledge. Anyone else?