Zaoka Posted March 18, 2020 Share Posted March 18, 2020 (edited) 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 Link to comment Share on other sites More sharing options...
seadoggie01 Posted March 18, 2020 Share Posted March 18, 2020 $oExcel is the Application, so Application.Application won't really do anything. Try using $oWorkbook instead of $oExcel.Application All my code provided is Public Domain... but it may not work. Use it, change it, break it, whatever you want. Spoiler My Humble Contributions:Personal Function Documentation - A personal HelpFile for your functionsAcro.au3 UDF - Automating Acrobat ProToDo Finder - Find #ToDo: lines in your scriptsUI-SimpleWrappers UDF - Use UI Automation more Simply-erKeePass UDF - Automate KeePass, a password managerInputBoxes - Simple Input boxes for various variable types Link to comment Share on other sites More sharing options...
Zaoka Posted March 18, 2020 Author Share Posted March 18, 2020 (edited) 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 Link to comment Share on other sites More sharing options...
Zaoka Posted March 19, 2020 Author Share Posted March 19, 2020 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? Link to comment Share on other sites More sharing options...
seadoggie01 Posted March 19, 2020 Share Posted March 19, 2020 You have CurrentPageName in your VBA, but only CurrentPage in AutoIt Zaoka 1 All my code provided is Public Domain... but it may not work. Use it, change it, break it, whatever you want. Spoiler My Humble Contributions:Personal Function Documentation - A personal HelpFile for your functionsAcro.au3 UDF - Automating Acrobat ProToDo Finder - Find #ToDo: lines in your scriptsUI-SimpleWrappers UDF - Use UI Automation more Simply-erKeePass UDF - Automate KeePass, a password managerInputBoxes - Simple Input boxes for various variable types Link to comment Share on other sites More sharing options...
Zaoka Posted March 19, 2020 Author Share Posted March 19, 2020 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&"]" Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now