none
VBNet Programmatically filtering a pivot table date filter RRS feed

  • Question

  • I have a VB Desktop application that opens an existing Excel spreadsheet that includes a sheet that contains a pivot table with three filters, W/E date, Month Number and Project Number.

    Programmatically I would like to be able to open the spreadsheet, clear the filters and then set the W/E date.

    My code is as follows:

                Dim xlApp As Excel.Application
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim pt As Excel.PivotTable
                Dim Field As Excel.PivotField
                Dim PI As Excel.PivotItem
    
                xlApp = New Excel.Application
    
                Try
                    xlApp.Visible = True
    
                    xlWorkBook = xlApp.Workbooks.Open(Filename:=TextBoxSpreadsheetFilePath.Text, [ReadOnly]:=True)
                    '+
                    '   Check to see if the Project Report sheet exists
                    '-
                    For Each xs In xlWorkBook.Sheets
                        If xs.Name = "Project Report" Then
                            MatchMade = True
                            Exit For
                        End If
                    Next
                Catch ex As Exception
                    ToolStripStatusLabelInformation.Text = "Unable to open spreadsheet .... " & ex.Message
                    Application.DoEvents()
                End Try
    
                xlWorkSheet = xlWorkBook.Worksheets("Project Report")
    
                pt = xlWorkSheet.PivotTables("PivotTable1")
                Field = pt.PivotFields("W/E Date")
    
                With pt
                            Field.ClearAllFilters()
                            pt.RefreshTable()
                End With
    
                xlWorkSheet.Cells(CheckWKMTHNORow, CheckWKMTHNOColumn + 1).value = "Oct"
                xlWorkSheet.Cells(CheckJobNoRow, CheckJobNoColumn).value = "277"
                xlWorkSheet.Cells(CheckWEDateRow, CheckWEDateColumn + 1).value = "07/10/2016"
    
    Up to the last line the "Oct" and "277" values are accepted by their corresponding filters, i.e.

    Filters

    But when I try to insert a date that I know to be valid in the W/E Date filter I get an error "'42561' is not an item of this field."

    Error

    I have tried updating the W/E Date filter independently from the other two filters and tried using a string, and a date defined variable but with no luck.

    I suspect that there might be a better way to apply the filters than updating the cells with the values, so if there is I would appreciate some guidance on that but also advice on any method that would allow me to successfully apply a date filter to the W/E Date.

    Just as a follow on question I would also like to be able to collapse the pivot table to display only its top sub-headings, i.e. just to show the headings Estimating, Health & Safety, Holiday Paid Previous year - any gudiance on how would I acheive that would also be most welcome.

    Headings

    Best regards

    Martin

    Tuesday, October 25, 2016 11:22 PM

Answers

  • Hi,

    To change the filter value, we need to use PivotField.CurrentPage property

            Field = pt.PivotFields("DATE")
     
            With pt
                Field.ClearAllFilters()
                pt.RefreshTable()
            End With
            Field.CurrentPage = "10/1/2016"

    >>just to show the headings Estimating, Health & Safety, Holiday Paid Previous year

    I think you are referring to Expand/Collapse button. You could use PivotField.ShowDetail property to expand or collapse.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Martin C 007 Wednesday, October 26, 2016 8:40 AM
    Wednesday, October 26, 2016 3:34 AM
    Moderator

All replies

  • To Filter or Find you have to use the text as it is shown cell.

    And for filtering a particular Date you have to use Type:=xlSpecifiDate in PivotFilters.Add method.

    ALhough below sample is in VBA, but this is given just to show you how it can be done. Hope you can do that in your language.

    Sub Sample()
    
        Dim Field As PivotField
        
        Set Field = ActiveSheet.PivotTables(1).PivotFields(1)
        
        Field.PivotFilters.Add Type:=xlSpecificDate, _
            Value1:=Format(ActiveCell.Value, Field.NumberFormat)
    
    
    End Sub
    
    For 2nd Question, Start Record Macro, then do the steps manually, stop recording, see what excel recorded. Later you just need to change the Hard Coded Cell Address, Table Name and Field Name.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 26, 2016 3:23 AM
    Answerer
  • Hi,

    To change the filter value, we need to use PivotField.CurrentPage property

            Field = pt.PivotFields("DATE")
     
            With pt
                Field.ClearAllFilters()
                pt.RefreshTable()
            End With
            Field.CurrentPage = "10/1/2016"

    >>just to show the headings Estimating, Health & Safety, Holiday Paid Previous year

    I think you are referring to Expand/Collapse button. You could use PivotField.ShowDetail property to expand or collapse.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Martin C 007 Wednesday, October 26, 2016 8:40 AM
    Wednesday, October 26, 2016 3:34 AM
    Moderator
  • Thankyou Celeste

    I have tried the following

    Field = pt.PivotFields("WK/MTH No.")

    Field.CurrentPage = "Oct"

    Field = pt.PivotFields("Job No.") Field.CurrentPage = "277"

    Field = pt.PivotFields("W/E Date") Field.CurrentPage = "07/10/2016"


    The first two work but the last one causes an error.

    Error

    I have tried setting it as a text "07/10/2016" and a date variable as below:

    Dim tempdate As Date
    tempdate = "07/10/2016"
    
    Field = pt.PivotFields("W/E Date")
    Field.CurrentPage = Format(tempdate, "dd/MM/yyyy")
    
    

    Just to eliminate the possibility that the passed date value could be invalid I can select the date I am using manually.

    Filter

    With regards the collapsing I have also tried Field.ShowDetails = false and although it doesn't generate an error it doesn't collapse the Pivot Table either.

    Wednesday, October 26, 2016 8:04 AM
  • Hi,

    Since we don’t have your data and file, I suggest you record a macro to filter and collapse, check the code to find the method which works for this W/E Date field.

    Or you could share us a sample file with data, we could help you check what causes the issue.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 26, 2016 8:30 AM
    Moderator
  • Thanks Asadulla Javed

    That would be okay if I was adding a filter, but I am using a filter that already exists.

    However, I did record the macro and I was able to confirm that it used the method suggested by Celeste Li

    Field = pt.PivotFields("W/E Date")
    Field.CurrentPage = "07/10/2016"

    However, what I noticed is that the macro entered the date using the mm/dd/yyyy format, instead of the displayed dd/mm/yyyy format.

    I have tried that and found that although 

    Field = pt.PivotFields("W/E Date")
    Field.CurrentPage = "10/07/2016"

    Doesn't work, it doesn't like the 07's zero prefix

    Field = pt.PivotFields("W/E Date")
    Field.CurrentPage = "10/7/2016"

    Does work.

    I now just need to work out how to apply the ShowDetails= false as I don't even know how to collapse all the fileds just using Excel without going through each group and collapsing them in turn, maybe I have to do the same programmatically.

    Martin

    Wednesday, October 26, 2016 8:40 AM
  • Celeste Li

    See my reply to Asadulla Javed - seems to only like the mm/dd/yyyy date format.

    Thanks, 

    M

    Wednesday, October 26, 2016 8:42 AM
  • The code was a sample only. Excel uses the text which is displayed at cell for filtering or finding.

    If a column is formatted as d-MM you have to use the search value in that formatting. Excel stores date as a serial number, if you use a cell's value, excel will filter with tht value directly and return error. Because it will filter if it is in same formatting with column.

    That is why I used Field.NumberFOrmat for passing Value1 to PivtoFilters.Add. You may do it differently but you must have to format the search criteria with columns number format.


    Best Regards,
    Asadulla Javed,
    Jadavpore & Asansol

    Wednesday, October 26, 2016 8:56 AM
    Answerer
  • Hi Celeste

    The only method I know when using Excel is to select all of the groups and then use the right-click Expand/Collapse, Collapes option and when I record a macro I get the following which isn't much help.

        Range("A6:A40").Select
        Range("A40").Activate
        Selection.ShowDetail = False

    A sample spreadsheet is available here Sample Spreadsheet and below is what I am trying to achieve, i.e. to ensure that the pivot table is in the format:

    Pivot Table Format 

    Wednesday, October 26, 2016 4:43 PM
  • All Sorted

    I just needed to select the right field 

                    Field = pt.PivotFields("Job Name")
                    Field.ShowDetail = False

    That did it.

    Thanks for all your help.

    M

    Wednesday, October 26, 2016 4:58 PM