Forum Discussion

NateP86's avatar
NateP86
Copper Contributor
Oct 16, 2023

Set a value for a field in a Power Pivot Table

Hi everyone,

 

I am trying to set a specific value in a field of a power pivot table, using VBA.

I discovered that working with a pivot table and a power pivot table require different approaches.

 

The field named "WeekNo" is the one for which I would like to select the number of the year's week I need. I tried a lot of alternatives and the following is the only one that doens't throw me an error of any kind. The problem is that it doesn't do anything.

 

The cleaning of the field works, whilst the last sentence, the 20th, goes empty.

 

Sub Filter()

    Dim wtdEMEA As PivotTable
    Dim filterValue As Long
    Dim fieldWeek As CubeField
    
    ' I read the power pivot table
    Set wtdEMEA = ThisWorkbook.Sheets("EMEA Channels").PivotTables("WTD EMEA")
    
    ' I take the value to filter in the power pivot table (es. 41)
    filterValue = Worksheets("Cover").Range("G42").Value
    
    ' I take the field
    Set fieldWeek = wtdEMEA.CubeFields("[Calendar].[WeekNo]")
    
    'I clear the field
    fieldWeek.ClearManualFilter
    
    'I try to set the field with the value taken before in the cell G42
    fieldWeek.PivotFields("[Calendar].[WeekNo].[WeekNo]").VisibleItemsList = Array(filterValue)
       
End Sub

 

Can anyone give me an hand?

The only line to fix should be the 20th, all the above are fine and I don't want to change them being simple and clear.

 

Thank you.

Nate

 

excel
Macros and VBA
  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Oct 16, 2023

    Hi NateP86,

    The issue is likely with the way you are trying to set the value for the field.

    You can try this solution for setting a value for a field in a Power Pivot table using VBA:

     

     

    Sub SetFieldValueInPowerPivotTable(pivotTable As PivotTable, fieldName As String, fieldValue As String)

    ' Get the CubeField object for the specified field.
    Dim field As CubeField
    Set field = pivotTable.CubeFields(fieldName)

    ' Clear any existing filter on the field.
    field.ClearManualFilter

    ' Set the CurrentPage property of the field to the specified value.
    field.CurrentPage = fieldValue

End Sub

     

    This macro is the same as the one in my previous response, but I have renamed it to SetFieldValueInPowerPivotTable to make it more clear what it does.

    To use this macro, simply pass in the following arguments:

    • pivotTable: The PivotTable object that you want to set the value in.
    • fieldName: The name of the field that you want to set the value in.
    • fieldValue: The value that you want to set the field to.

    For example, the following code would set the value of the WeekNo field in the WTD EMEA Power Pivot table to the value 41:

     

    Dim pivotTable As PivotTable
Set pivotTable = ThisWorkbook.Sheets("EMEA Channels").PivotTables("WTD EMEA")

Dim fieldName As String
fieldName = "[Calendar].[WeekNo]"

Dim fieldValue As String
fieldValue = "41"

Call SetFieldValueInPowerPivotTable(pivotTable, fieldName, fieldValue)

     


    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic
    (LinkedIn)

    • NateP86's avatar
      NateP86
      Copper Contributor
      Oct 16, 2023

      Hello LeonPavesic,

       

      first of all, many thanks for your reply.

       

      I tried, as per the attachment, the script you sent, hope properly 🙂

      It seems that CurrentPage method is not accepted for power pivot tables.

       

      Does it make sense for you?

      Maybe it's something that fits well with standard pivot table, what do you think?

       

      I happens the same just changing the 20th line with your suggestion.

       

      Sub Filter()

    Dim wtdEMEA As PivotTable
    Dim filterValue As Long
    Dim fieldWeek As CubeField
    
    ' I read the power pivot table
    Set wtdEMEA = ThisWorkbook.Sheets("EMEA Channels").PivotTables("WTD EMEA")
    
    ' I take the value to filter in the power pivot table (es. 41)
    filterValue = Worksheets("Cover").Range("G42").Value
    
    ' I take the field
    Set fieldWeek = wtdEMEA.CubeFields("[Calendar].[WeekNo]")
    
    'I clear the field
    fieldWeek.ClearManualFilter
    
    'I try to set the field with the value taken before in the cell G42
    fieldWeek.CurrentPage = filterValue
       
End Sub

       

      Please let me know what do you think.

       

      Thank you.

      Nate

Resources