VBS Filter Pivot Table with specific value from the list

Viewed 45

Using a VBS script, I need to open a Workbook with a Pivot Table connected to OLAP Cube, update the data, select a specific region in the filter (for example, Oslo), save the Workbook and close it.

Here's what the Pivot table looks like: enter image description here

Unfortunately, the code doesn't select the filter and doesn't set it to "Oslo". It only opens the file and does nothing else.

Here's my VBS-code:

Dim oExcel
Dim myPivotField
Dim PvtItm

Set oExcel = CreateObject("Excel.Application") 

oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False

Set oWorkbook = oExcel.Workbooks.Open("C:\Users\User\Documents\Folder1\Test\Workbook.xlsx")
Set myPivotField  = oWorkbook.WorkSheets(2).PivotTables(1).PivotFields("[Workbook].[FederationUnitName].[FederationUnitName]")

oWorkbook.RefreshAll
myPivotField.ClearAllFilters
    
For Each PvtItm In myPivotField.PivotItems
    Select Case PvtItm.Name
        Case "[Workbook].[FederationUnitName].&[Oslo]"
            PvtIt.Visible = True
        Case Else 
            PvtIt.Visible = False
    End Select
Next

Set myPivotField = Nothing
Set PvtItm = Nothing

oWorkbook.Save

Any help would be really appreciated.

1 Answers

Ultimately, through trial and error, I managed to find a solution to my own question. I don't know exactly why "Array" works with a single selection, but it worked for me. Hope it will be useful to somebody.

Dim oExcel
Dim myPivotField
Dim PvtItm

Set oExcel = CreateObject("Excel.Application") 

oExcel.Visible = True
oExcel.DisplayAlerts = False
oExcel.AskToUpdateLinks = False
oExcel.AlertBeforeOverwriting = False

Set oWorkbook = oExcel.Workbooks.Open("C:\Users\User\Documents\Folder1\Test\Workbook.xlsx")
Set myPivotField  = oWorkbook.WorkSheets(2).PivotTables(1).PivotFields("[Workbook].[FederationUnitName].[FederationUnitName]")

oWorkbook.RefreshAll
myPivotField.ClearAllFilters

myPivotField.VisibleItemsList = Array("[Workbook].[FederationUnitName].&[Oslo]")


Set myPivotField = Nothing
Set PvtItm = Nothing

oWorkbook.Save
Related