Zaoka Posted April 7, 2020 Share Posted April 7, 2020 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. Link to comment Share on other sites More sharing options...
water Posted April 7, 2020 Share Posted April 7, 2020 (edited) 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 1 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Zaoka Posted April 7, 2020 Author Share Posted April 7, 2020 52 minutes ago, water said: Array is a VBA function. I think you need to provide an array when using AutoIt. Untested: I try it, it runs without error, but filters don't change Link to comment Share on other sites More sharing options...
water Posted April 7, 2020 Share Posted April 7, 2020 I haven't worked wih Pivottables till now. Do you know what the "&" in the array is used for? My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Zaoka Posted April 7, 2020 Author Share Posted April 7, 2020 12 minutes ago, water said: I haven't worked wih Pivottables till now. Do you know what the "&" in the array is used for? "&" 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 Link to comment Share on other sites More sharing options...
water Posted April 7, 2020 Share Posted April 7, 2020 That's way over my head I hope some Excel power pivot guru chimes in. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
seadoggie01 Posted April 8, 2020 Share Posted April 8, 2020 (edited) On 4/7/2020 at 8:40 AM, water said: Do you know what the "&" in the array is used for? 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 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 April 8, 2020 Author Share Posted April 8, 2020 7 hours ago, 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) 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 Link to comment Share on other sites More sharing options...
seadoggie01 Posted April 9, 2020 Share Posted April 9, 2020 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 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 April 9, 2020 Author Share Posted April 9, 2020 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 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