Jump to content

Filter multiple WEEKs PowerPivot Array


Recommended Posts

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 post
Share on other sites

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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 post
Share on other sites

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 - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
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 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 functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites
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 post
Share on other sites

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

 

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 functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager

Link to post
Share on other sites

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 post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By TheAlienDoctor
      Hi, I was looking into creating a script that would detect if a file exists, then move it (and in some cases rename it, depending on the file) as well as write to a log file.
      The issue is, there is a lot of files that need to be moved, sometimes some files will exist and others won't depending on the use-case. However if a file does exist, it will always be going into the same directory with the same name.
      Currently I have an array nested inside of the array, with each array inside that array having both the old and new directory, and then a For loop to actually run through and do the file transferring. The issue I am having is how to call the Array inside of the array, because how do I specify  which the old directory is and which the new is?
      Global $FileTransfer[2000] = [Global $Dir1[2] = ["original dir 1", "new dir 1"], Global $Dir2[2] = ["original dir 2", "new dir 2"]] For $FileTransfer = [0] To [1] Step +1 If FileExists({original dir}) Then FileMove({original dir}, new dir, 1) FileOpen("log.latest.txt", 1) FileWrite("log.latest.txt", "{original dir} found, moved it to new dir." & @CRLF) FileClose("log.latest.txt") Else FileOpen("log_latest.txt", 1) FileWrite("log_latest.txt", "{original dir} not found, ignoreing it." & @CRLF) FileClose("log_latest.txt") EndIf Next I have put what I want the old and new directory to be for each array in {}, so hopefully its easier to tell which part is working and whats not.

      I am still reasonably new to AutoIT, any help is appreciated. Thankyou
    • By arunkw
      I have a spreadsheet - daily routine which has two columns: activity and time as shown here
      | Activity             | Time     | |----------------------|----------| | Sleep               |  6:00 am | | Toilet              |  6:15 am | | Get ready for gym  |  6:30 am | | Exercise            |  7:50 am | | ... more things      |  9:00 pm | | ... still more       | 10:45 pm | | Sleep               |  6:00 am |   I wanted to find out, say in C1 which activity is current for me using now() I.e., if it’s 6:45am on my watch, it should show me Exercise  in C1 Thanks to Adam D. PE, this formula works like magic to get the result =VLOOKUP(MOD(NOW(),1),{B2:B,A2:A},2,1)   Now, I want to reproduce same result in autoit, how to do that? To have easy solution say, I copy-paste spreadsheet data in array directly in code, right? Use for loop and run the above vlookup function and show the answer using tooltip. How to achieve this? please help.  
    • By goku200
      I have an Autoit script that lists files from a folder into an array list. Is there a way to separate the filenames by an underscore and include the id, version, name and date into separate columns in Excel.
      Example of filename:
      12345_v1.0_TEST Name [12345]_01.01.2022.html
      12345 would be in one column
      v1.0 would be in another column
      TEST Name [12345] would be in another column
      01.01.2022 would be in another column
      .html would be in another column
      Note: filenames always change each day.
      Here is my code that lists the files into column C and then writes the column Headers into Column D, E, F, G. Just need some help with separating them into columns by the _ delimiter
       
    • By SkysLastChance
      I am having a issue of whenever I try to _Excel_RangeWrite a formula that references another workbook I am getting an error @4 and @extended -2147352567

      #include <Excel.au3> #include <MsgBoxConstants.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookNew($oExcel) _Excel_RangeWrite($oWorkBook,Default,"=IF(D2=D1,"",VLOOKUP(D2,'J:\Temporary Files\FolderName\FileName.xlsx'!$A:$B,2,0))","W2",False) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.") I am not a 100% sure, but I am guessing this is because I have "" and '' in the formula. However, I am not sure how this can best be resolved. 
       
       
    • By SkysLastChance
      I am trying to autofill a range. I am getting stuck and I don't understand what I am doing wrong. 
      My goal is to auto fill some formulas that are next to a pivot table in columns A-C. 
      _Excel_RangeWrite($oNewWorkBook,Default,"2000","D3") _Excel_RangeWrite($oNewWorkBook,Default,"=(B3-D3)","E3") _Excel_RangeWrite($oNewWorkBook,Default,"100","F3") _Excel_RangeWrite($oNewWorkBook,Default,"=(C3-F3)","G3") $oNewWorkbook.ActiveSheet.Range("D3:G3").Select With $oNewWorkbook .Selection.AutoFill(.Range("D3:G77"),0) EndWith The data is not auto filling. 
      Hoping someone can point me in the right direction. 
×
×
  • Create New...