Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- $xlRowField = 1
- $xlColumnField = 2
- $xlDataField = 4
- $xlCount = -4112
- $Excel.Visible = $True
- $outputWorkBook = $Excel.Workbooks.Open("FILENAME.xlsx", $True, $True)
- pivotTable = $outputWorkBook.ActiveSheet.PivotTableWizard()
- $pivotTable.PivotFields("columnWithNumbers").Orientation = [int]$xlRowField
- $pivotTable.PivotFields("filename").Orientation = [int]$xlColumnField
- $pivotDataCount = $pivotTable.PivotFields("columnWithNumbers")
- $pivotDataCount.Orientation = [int]$xlDataField
- $pivotDataCount.Function = [int]$xlCount
- Sub Macro1()
- '
- ' Macro1 Macro
- '
- '
- Sheets.Add
- ActiveWorkbook.Worksheets("Sheet2").PivotTables("PivotTable1").PivotCache. _
- CreatePivotTable TableDestination:="Sheet4!R3C1", TableName:="PivotTable3" _
- , DefaultVersion:=6
- Sheets("Sheet4").Select
- Cells(3, 1).Select
- With ActiveSheet.PivotTables("PivotTable3")
- .ColumnGrand = True
- .HasAutoFormat = True
- .DisplayErrorString = False
- .DisplayNullString = True
- .EnableDrilldown = True
- .ErrorString = ""
- .MergeLabels = False
- .NullString = ""
- .PageFieldOrder = 2
- .PageFieldWrapCount = 0
- .PreserveFormatting = True
- .RowGrand = True
- .SaveData = True
- .PrintTitles = False
- .RepeatItemsOnEachPrintedPage = True
- .TotalsAnnotation = False
- .CompactRowIndent = 1
- .InGridDropZones = False
- .DisplayFieldCaptions = True
- .DisplayMemberPropertyTooltips = False
- .DisplayContextTooltips = True
- .ShowDrillIndicators = True
- .PrintDrillIndicators = False
- .AllowMultipleFilters = False
- .SortUsingCustomLists = True
- .FieldListSortAscending = False
- .ShowValuesRow = False
- .CalculatedMembersInFilters = False
- .RowAxisLayout xlCompactRow
- End With
- With ActiveSheet.PivotTables("PivotTable3").PivotCache
- .RefreshOnFileOpen = False
- .MissingItemsLimit = xlMissingItemsDefault
- End With
- ActiveSheet.PivotTables("PivotTable3").RepeatAllLabels xlRepeatLabels
- With ActiveSheet.PivotTables("PivotTable3").PivotFields("id")
- .Orientation = xlRowField
- .Position = 1
- End With
- With ActiveSheet.PivotTables("PivotTable3").PivotFields("filename")
- .Orientation = xlColumnField
- .Position = 1
- End With
- ActiveSheet.PivotTables("PivotTable3").AddDataField ActiveSheet.PivotTables( _
- "PivotTable3").PivotFields("id"), "Sum of id", xlSum
- With ActiveSheet.PivotTables("PivotTable3").PivotFields("Sum of id")
- .Caption = "Count of id"
- .Function = xlCount
- End With
- Range("A5").Select
- ActiveSheet.PivotTables("PivotTable3").PivotFields("id").PivotFilters.Add2 _
- Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables( _
- "PivotTable3").PivotFields("Count of id"), Value1:=1
- End Sub
Add Comment
Please, Sign In to add comment