Slicer excel VBA to AHK

Get help with using AutoHotkey and its commands and hotkeys
blue83
Posts: 143
Joined: 11 Apr 2018, 06:38

Slicer excel VBA to AHK

20 Apr 2020, 03:24

Hi can anyone help me to translate this code to AHK?

Code: Select all

    ActiveWorkbook.SlicerCaches("Slicer_Date.Month3").VisibleSlicerItemsList = _
        Array( _
        "[Date].[Month].&[4]", "[Date].[Month].&[5]")
    ActiveWorkbook.SlicerCaches("Slicer_Date.Day_In_Month").VisibleSlicerItemsList _
        = Array( _
        "[Date].[Day In Month].&[1]", "[Date].[Day In Month].&[10]", _
        "[Date].[Day In Month].&[11]", "[Date].[Day In Month].&[2]", _
        "[Date].[Day In Month].&[3]", "[Date].[Day In Month].&[4]", _
        "[Date].[Day In Month].&[5]", "[Date].[Day In Month].&[6]", _
        "[Date].[Day In Month].&[7]", "[Date].[Day In Month].&[8]", _
        "[Date].[Day In Month].&[9]")
BNOLI
Posts: 548
Joined: 23 Mar 2020, 03:55

Re: Slicer excel VBA to AHK

21 Apr 2020, 00:27

Remember to use [code]CODE[/code]-tags for your multi-line scripts. Stay safe, stay inside, and remember washing your hands for 20 sec !
blue83
Posts: 143
Joined: 11 Apr 2018, 06:38

Re: Slicer excel VBA to AHK

21 Apr 2020, 01:22

Hi @BNOLI ,

I want manipulate with slicers in excel, it looks like this.

Image
Attachments
Capture.PNG
Capture.PNG (44.66 KiB) Viewed 449 times
blue83
Posts: 143
Joined: 11 Apr 2018, 06:38

Re: Slicer excel VBA to AHK

24 Apr 2020, 06:29

Any help with this?
Or at least a clue how to convert VBA to AHK?

Thanks,
blue
User avatar
Datapoint
Posts: 76
Joined: 18 Mar 2018, 17:06

Re: Slicer excel VBA to AHK

24 Apr 2020, 11:12

I didn't test this but I think it should be something like this:

Code: Select all

/*     ActiveWorkbook.SlicerCaches("Slicer_Date.Month3").VisibleSlicerItemsList = _
 *         Array( _
 *         "[Date].[Month].&[4]", "[Date].[Month].&[5]")
 *     ActiveWorkbook.SlicerCaches("Slicer_Date.Day_In_Month").VisibleSlicerItemsList _
 *         = Array( _
 *         "[Date].[Day In Month].&[1]", "[Date].[Day In Month].&[10]", _
 *         "[Date].[Day In Month].&[11]", "[Date].[Day In Month].&[2]", _
 *         "[Date].[Day In Month].&[3]", "[Date].[Day In Month].&[4]", _
 *         "[Date].[Day In Month].&[5]", "[Date].[Day In Month].&[6]", _
 *         "[Date].[Day In Month].&[7]", "[Date].[Day In Month].&[8]", _
 *         "[Date].[Day In Month].&[9]") 
 */
 
 xlApp := ComObjActive("Excel.Application") ;connect to existing instance of excel
 xlActWB := xlApp.ActiveWorkbook
 VT_VARIANT := 0xC ; https://www.autohotkey.com/docs/commands/ComObjType.htm#vt
 
 myArray := ComObjArray(VT_VARIANT, 2) ; create COM array with 2 items (1 dimensional)
 myArray[0] := "[Date].[Month].&[4]" ; COM array index start at 0, not 1
 myArray[1] := "[Date].[Month].&[5]"
 xlActWB.SlicerCaches("Slicer_Date.Month3").VisibleSlicerItemsList := myArray
 
myArray := ComObjArray(VT_VARIANT, 11) ; create COM array with 11 items (1 dimensional)
myArray[0] := "[Date].[Day In Month].&[1]"
myArray[1] := "[Date].[Day In Month].&[10]"
myArray[2] := "[Date].[Day In Month].&[11]"
myArray[3] := "[Date].[Day In Month].&[2]"
myArray[4] := "[Date].[Day In Month].&[3]"
myArray[5] := "[Date].[Day In Month].&[4]"
myArray[6] := "[Date].[Day In Month].&[5]"
myArray[7] := "[Date].[Day In Month].&[6]"
myArray[8] := "[Date].[Day In Month].&[7]"
myArray[9] := "[Date].[Day In Month].&[8]"
myArray[10] := "[Date].[Day In Month].&[9]"
xlActWB.SlicerCaches("Slicer_Date.Day_In_Month").VisibleSlicerItemsList := myArray
 
xlApp := xlActWB := "" ; Clear variables containing COM ojects when done
blue83
Posts: 143
Joined: 11 Apr 2018, 06:38

Re: Slicer excel VBA to AHK

24 Apr 2020, 13:14

Hi @Datapoint ,

Really thank you for your effort, dont know how to thank you for that.

Return to “Ask For Help”

Who is online

Users browsing this forum: boiler, braunbaer, malcev, mikeyww, neo256, newbieforever, Smile_, swub, vmech and 53 guests