Excel VBA: Set a Power Pivot WeekNo Field Value
Question details
The user needs to set a selected week number in the WeekNo field of a Power Pivot table by using VBA.
- Product
- Microsoft Excel Power Pivot and VBA
- Device & OS
- not provided
- Scenario
- A VBA procedure clears a Power Pivot filter and then needs to apply a specific week value, such as 41, to the WeekNo field.
- Observed behavior
- The manual filter is cleared successfully, but assigning the selected week number does not apply the value to the CubeField.
Confirm the Power Pivot table name, worksheet name, and model hierarchy name in Excel. Use the exact CubeField hierarchy name, such as [Calendar].[WeekNo], and pass the week number as text.
Set the Power Pivot WeekNo CubeField with CurrentPage
Use this approach when the field is a CubeField in a Power Pivot table and the filter must be reset before selecting one week.
Power Pivot fields require the CubeField object and its CurrentPage property. Clear the existing manual filter first, then assign the target week as a string that matches a value in the model hierarchy.
In Excel, press Alt+F11, choose Insert > Module, and add this VBA procedure: Sub SetFieldValueInPowerPivotTable(pivotTable As PivotTable, fieldName As String, fieldValue As String) Dim field As CubeField Set field = pivotTable.CubeFields(fieldName) field.ClearManualFilter field.CurrentPage = fieldValue End Sub
In the same module, use ThisWorkbook.Worksheets("EMEA Channels").PivotTables("WTD EMEA") to reference the worksheet and Power Pivot table containing the WeekNo field.
Call the procedure with the exact model hierarchy name and a text value, for example: Dim pivotTable As PivotTable Set pivotTable = ThisWorkbook.Worksheets("EMEA Channels").PivotTables("WTD EMEA") SetFieldValueInPowerPivotTable pivotTable, "[Calendar].[WeekNo]", "41"
Return to Excel, enable macros if prompted, and run the procedure from Developer > Macros. Check that the Power Pivot table displays week 41 in the WeekNo filter.
Move Power Pivot Reports to a Free, Compatible WPS Office Workflow
WPS Office is a free, lightweight Microsoft Office alternative for opening, editing, and sharing compatible spreadsheet files. Its familiar interface helps with everyday workbook migration, while specialized Excel Power Pivot VBA automation may still require Microsoft Excel.
- 1. Install WPS Office: Download and open WPS Office, then launch WPS Spreadsheets from the home screen.
- 2. Open the workbook: Choose Open from the WPS Spreadsheets start page and select the Excel .xlsx or .xls workbook.
- 3. Review spreadsheet compatibility: Check the Power Pivot report layout, formulas, and filters in WPS Spreadsheets; run specialized Power Pivot VBA in Microsoft Excel when required.
Frequently Asked Questions
Why does assigning a value to a Power Pivot field fail with a regular PivotField object?
A Power Pivot field is exposed through the CubeFields collection. Set the field with Set field = pivotTable.CubeFields("[Calendar].[WeekNo]") and then use CurrentPage.
Should the WeekNo value be passed as a number or text?
Pass the selected value as text, such as "41". The text must match the member value stored in the Power Pivot model.
Why must ClearManualFilter run before CurrentPage?
An existing manual filter can prevent the new page selection from being applied. Calling field.ClearManualFilter first resets the filter state before CurrentPage is assigned.
What should I check if [Calendar].[WeekNo] is not found?
Verify the exact hierarchy name in the Power Pivot data model and the PivotTable's CubeFields collection. The field name must match the model hierarchy, including brackets and parent hierarchy names.
