locked
How to get from the PivotTable (excel) only visible items from the specified field for OLAP technology. RRS feed

  • General discussion

  • Hello!
    I am developing anapplicationfor data analysisPivotTableMS EXCEL.
    I had aproblem with gettingthe datavisibleon the screenafter applyingfiltershidingpartI do not needdata.
    I need toget thename or numberof linesvisibleon the screen inoneparticular field.

     ' БЛОК ПОДГОТОВКИ СВОДНЫХ ТАБЛИЦ РАСКРАИВАЕМЫХ ДЕТАЛЕЙ.
    
            xlApp.Workbooks("Раскрой.xlsx").Sheets("Таблицы").Activate()
            raskroy = xlApp.Workbooks("Раскрой.xlsx").Sheets("Таблицы").range(xlApp.Cells(1, 6), xlApp.Cells(i_raskr, 18))
            xlApp.Workbooks("Раскрой.xlsx").Sheets("Сводная").Activate() ' выбор ячейки для вставки сводной таблицы
            xlApp.Cells(5, 1).activate()
            ' Список номеров заказов
            xlApp.ActiveSheet.PivotTableWizard(SourceType:=XlPivotTableSourceType.xlDatabase, SourceData:=raskroy, TableName:="Раскрой", RowGrand:=False, ColumnGrand:=False)
    
            With xlApp.ActiveSheet.PivotTables("Раскрой")
                .InGridDropZones = False
                .DisplayImmediateItems = True
                .FieldListSortAscending = True
                .DisplayFieldCaptions = False
                .HasAutoFormat = False
            End With
            With xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("Сортамент")
                .Orientation = XlPivotFieldOrientation.xlRowField
                .Position = 1
                .subtotals(1) = False
            End With
            With xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("Обозначение")
                .Orientation = XlPivotFieldOrientation.xlRowField
                .Position = 2
                .subtotals(1) = False
            End With
            With xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("L, м")
                .Orientation = XlPivotFieldOrientation.xlRowField
                .Position = 3
                .subtotals(1) = False
            End With
    
            With xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("B, м")
                .Orientation = XlPivotFieldOrientation.xlPageField
                .Position = 1
                .PivotItems("0").Visible = False
                .PivotItems("B, м").Visible = False
                .PivotItems("(blank)").Visible = False
            End With
            With xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("B, м")
                .Orientation = XlPivotFieldOrientation.xlRowField
                .Position = 4
            End With
            xlApp.ActiveSheet.PivotTables("Раскрой").AddDataField(xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("Кол-во в заказе, шт"), _
            "Количество по полю Кол-во в заказе, шт", XlConsolidationFunction.xlSum)
    
            xlApp.Workbooks("Раскрой.xlsx").Save()
    
            Dim dfg As Integer
    
            dfg = xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("Сортамент").visibleitems.count
    
            If xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("Сортамент").pivotitems(1).visible Then
                dfg = dfg + 1
            End If
    
            dfg = xlApp.ActiveSheet.PivotTables("Раскрой").PivotFields("Сортамент").autoshowcount
    
          
            xlApp.Workbooks("Раскрой.xlsx").Save()

    Friday, December 6, 2013 1:33 PM