Convert VBA 'slicer' selection to WB

[stanl]

I just created a compiled script that performs an SQL query, clears and populates a dynamic Range in Excel and refreshes a Pivot cache which operates with slicers. Once of the slicers controls the date of the data. It is updated daily, but when refreshed the date slicer is set to the previous date. a VBA snippet to access the slicer is

Code Select Expand


ActiveSheet.Shapes.Range(Array("Date 1")).Select
    With ActiveWorkbook.SlicerCaches("Slicer_Date")
        .SlicerItems("5/17/2014").Selected = True


the date within the 3rd line would be variable and set to the most current date in the updated Pivot cache. It is the Range(Array("Date 1")) that has me a bit puzzled in terms of converting to WB.

[Deana]

Take a look at this code sample:

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Load~WIL~Array~into~Excel.txt

[stanl]

Take a look at this code sample:

http://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/tsleft.web+WinBatch/OLE~COM~ADO~CDO~ADSI~LDAP/OLE~with~Excel+Load~WIL~Array~into~Excel.txt

That was my sample and circa 2005. Slicers is they are held as arrays, so basically I have to select an array..  The code sample creates a WB array from the outside, I need to create it from the inside.

[Deana]

Did you try this:

Code (winbatch) Select Expand

aR = ArrDimension(1)
aR[0] = "Date 1"
objActiveSheet.Shapes.Range(aR).Select

[stanl]

Did you try this:

Code (winbatch) Select Expand

aR = ArrDimension(1)
aR[0] = "Date 1"
objActiveSheet.Shapes.Range(aR).Select

Thank you. This worked. Just need to variabalize the date.

Code Select Expand


aR = ArrDimension(1)
aR[0] = "Date 1"
oXL.ActiveSheet.Shapes.Range(aR).Select
oXL.ActiveWorkbook.SlicerCaches("Slicer_Date").SlicerItems("5/20/2014").Selected = @True 


[Deana]

Other options:

Code (winbatch) Select Expand


aR = Arrayize( "Date 1", "" )
objActiveSheet.Shapes.Range(aR).Select


And

Code (winbatch) Select Expand


objActiveSheet.Shapes.Range(Arrayize( "Date 1", "" )).Select


[stanl]

Yes, thanks.... didn't mean to ask you to do my job, but now I know how I have to approach slicers. Some of the templates I will be working with have multiple slicers and it is good to know they follow the VBA hierarchy (for lac of a better phrase). I'll be practicing writing a generic UDF to obtain slicer names, slicer items and be able to set or un-filter each for an initial presentation. If the code looks promising I'll post with a demo sliced Pivot.

Thanks again