Filter multiple WEEKs PowerPivot Array

[Zaoka]

Hi guys

Need little help with filtering.

I'm trying to filter specific weeks from power pivot table using this code recorded with Excel VBA :
 

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb")
    $oWorkbook.PivotTables("PivotTable1").PivotFields( _
        "[Report 2].[Week].[Week]").VisibleItemsList = Array( _
        "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _
        "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _
        "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]")

But get error

error: Array(): undefined function.

Not sure how to resolve this.

[water]

Array is a VBA function. I think you need to provide an array when using AutoIt.

Untested:

#include <Excel.au3>
Global $aArray[] = ["[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _
        "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _
        "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]"]
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb")
    $oWorkbook.PivotTables("PivotTable1").PivotFields("[Report 2].[Week].[Week]").VisibleItemsList = $aArray

 

Edited April 7, 2020 by water

[Zaoka]

  On 4/7/2020 at 10:45 AM, water said:

Array is a VBA function. I think you need to provide an array when using AutoIt.

Untested:

 

Expand  

I try it, it runs without error, but filters don't change

[water]

I haven't worked wih Pivottables till now. Do you know what the "&" in the array is used for?

[Zaoka]

  On 4/7/2020 at 12:40 PM, water said:

I haven't worked wih Pivottables till now. Do you know what the "&" in the array is used for?

Expand  

"&" is used in PowerPivot , it is DAX  Excell expression , regular pivot don't use it.  Not sure what exactly it is used for, i think it is used to bind formula conditions

[water]

That's way over my head
I hope some Excel power pivot guru chimes in.

[seadoggie01]

  On 4/7/2020 at 12:40 PM, water said:

Do you know what the "&" in the array is used for?

Expand  

Looks like it's used to refer to a column name (or the index of a column) in a database. Not 100% sure though.

@Zaoka Can you post the code that the Excel macro recorder generated? I wonder if there was maybe a typo (happens to the best of us... some more than others, like me :D)

Edited April 8, 2020 by seadoggie01

[Zaoka]

  On 4/8/2020 at 3:11 PM, seadoggie01 said:

@Zaoka Can you post the code that the Excel macro recorder generated? I wonder if there was maybe a typo (happens to the best of us... some more than others, like me :D)

Expand  

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.PivotTables("PivotTable1").PivotFields( _
        "[Report 2].[Week].[Week]").VisibleItemsList = Array( _
        "[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _
        "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _
        "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]")
End Sub

 

[seadoggie01]

I read something that said that not every PivotTable is available from the workbook level for some reason, so my only thought is to specify the Worksheet that the PivotTable is specified on. Try this: (filling it in of course )

#include <Excel.au3>
Global $aArray[] = ["[Report 2].[Week].&[10]", "[Report 2].[Week].&[11]", _
        "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _
        "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]"]
Global $oExcel = _Excel_Open()
If @error Then Exit ConsoleWrite("_Excel_Open()" & @CRLF)
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Orders.xlsb")
If @error Then Exit ConsoleWrite("_Excel_BookOpen()" & @CRLF)
Global $oSheet = $oWorkbook.Sheets("YourSheetName")
If @error Then Exit ConsoleWrite("$oWorkbook.Sheets" & @CRLF)
$oWorksheet.PivotTables("PivotTable1").PivotFields("[Report 2].[Week].[Week]").VisibleItemsList = $aArray

 

[Zaoka]

Hi tnx for this,

i only changed last row

$oSheet.PivotTables("PivotTable1").PivotFields("[Report 2].[Week].[Week]").VisibleItemsList = $aArray

So final code looks like this, tested it works

#include <Excel.au3>
Global $aArray[] = ["[Report 2].[Week].&[8]", "[Report 2].[Week].&[9]", _
        "[Report 2].[Week].&[12]", "[Report 2].[Week].&[13]", _
        "[Report 2].[Week].&[14]", "[Report 2].[Week].&[15]"]
Global $oExcel = _Excel_Open()
If @error Then Exit ConsoleWrite("_Excel_Open()" & @CRLF)
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\....\Order.xlsb")
If @error Then Exit ConsoleWrite("_Excel_BookOpen()" & @CRLF)
Global $oSheet = $oWorkbook.Sheets("Orders")
If @error Then Exit ConsoleWrite("$oWorkbook.Sheets" & @CRLF)
$oSheet.PivotTables("PivotTable1").PivotFields("[Report 2].[Week].[Week]").VisibleItemsList = $aArray

Thank you all