none
automating slicers RRS feed

  • Question

  • Hello,

    I am trying to automate an Excel 2010 report where the only manual touch point is a slicer. 

    I built scripting via a powershell com object that runs the report an emails it as html. 

    Is there a way to automate the slicer filter?

    Thank you for any help!

    Wednesday, September 6, 2017 1:36 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Thursday, September 7, 2017 9:54 AM
  • Hello,

    We are not familiar with powershell, here is the VBA code to filter using slicer, please modify based on your requirement.

    ActiveWorkbook.SlicerCaches("Slicer_Name").ClearAllFilters
    For i = 1 To ActiveWorkbook.SlicerCaches("Slicer_Name").SlicerItems.Count
    If ActiveWorkbook.SlicerCaches("Slicer_Name").SlicerItems(i).Value = "" Then
    ActiveWorkbook.SlicerCaches("Slicer_Name").SlicerItems(i).Selected = True
    Else
    ActiveWorkbook.SlicerCaches("Slicer_Name").SlicerItems(i).Selected = False
    End If

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 8, 2017 6:27 AM
    Moderator