Filter Yesterday WeekDay PowerPivot

[Zaoka]

Hi guys,

i have simple report in PowerPivot that shows Orders (Values) by Regions (Row) and Weeks (Columns). In Filter field is WeekDAYS (Monday,Tuesday,Wednesday,Thursday etc )

how to filter WeekDAYS Filed on WEEKDAYYesterday with autoit ?

my junky try

#include <Date.au3>
#include <Excel.au3>
Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1)

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb")
Sleep (5000)
$oExcel.ActiveWorkbook.RefreshAll
Sleep (5000)

$oExcel.Application.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)

 

Error result

$oExcel.Application.Sheets("PivotTable1").PivotTables("PivotTable1").PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)
$oExcel.Application^ ERROR

 

Edited March 18, 2020 by Zaoka

[seadoggie01]

$oExcel is the Application, so Application.Application won't really do anything. Try using $oWorkbook instead of $oExcel.Application

[Zaoka]

No luck with that 😓

 

but menage to remove all filters,

$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").ClearAllFilters

I think problem is with this part

PivotFields("WeekDAYS").PivotFilters($sWEEKDAYYesterday)

Edited March 18, 2020 by Zaoka

[Zaoka]

I made little progres, recorded macro with excel  and this is result

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]"). _
        ClearAllFilters
    Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]"). _
        CurrentPageName = "[Report 2].[WeekDAYS].&[Monday]"
End Sub

 

but when i insert it in autoit, only first part  "ClearAllFilters" is working, second part does not Filter anything

 

$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]").ClearAllFilters
Sleep (1000)
$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]").CurrentPage = "[Report 2].[WeekDAYS].&[Monday]"

 

some ideas or hints?

 

[seadoggie01]

You have CurrentPageName in your VBA, but only CurrentPage in AutoIt

[Zaoka]

Yes Finali it works now, tnx

 

#include <Date.au3>
#include <Excel.au3>
Local $sWEEKDAYYesterday = _DateDayOfWeek(@WDAY-1)

Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\.......\Orders.xlsb")


$oWorkbook.Sheets("Sheet1").PivotTables("PivotTable1").PivotFields("[Report 2].[WeekDAYS].[WeekDAYS]").CurrentPageName = "[Report 2].[WeekDAYS].&["&$sWEEKDAYYesterday&"]"