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
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.
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
Quote from: Deana on May 19, 2014, 01:51:50 PM
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 (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.
Did you try this:
aR = ArrDimension(1)
aR[0] = "Date 1"
objActiveSheet.Shapes.Range(aR).Select
Quote from: Deana on May 21, 2014, 07:57:53 AM
Did you try this:
aR = ArrDimension(1)
aR[0] = "Date 1"
objActiveSheet.Shapes.Range(aR).Select
Thank you. This worked. Just need to variabalize the date.
aR = ArrDimension(1)
aR[0] = "Date 1"
oXL.ActiveSheet.Shapes.Range(aR).Select
oXL.ActiveWorkbook.SlicerCaches("Slicer_Date").SlicerItems("5/20/2014").Selected = @True
Other options:
aR = Arrayize( "Date 1", "" )
objActiveSheet.Shapes.Range(aR).Select
And
objActiveSheet.Shapes.Range(Arrayize( "Date 1", "" )).Select
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