Forum Discussion
NateP86
Oct 16, 2023Copper Contributor
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
- LeonPavesicSilver Contributor
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)- NateP86Copper Contributor
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