'*******************************
'
' NAME: pivot_UsersbyManager.vbs
' Version 1.2
'
' AUTHOR: Brian Nickerson
'
' COMMENT: This script reads a csv file and adds a new worksheet called "UpdatingUsers".
'          This sheet contains a pivot table counting the number of users
'          and grouping them by manager and separating them by server.
'
'          In this example the 'xllastcell' address is determined programatically using the
'          specialcells method and the xllastcell constant value of 11. 
' 
'          The PivotTableWizard is used to create the Pivot table in the "UpdatingUsers" worksheet
'          ShowPivotTableFieldList and CommandBars are suppressed.  These are part of the Wizard's GUI interface
'          Subtotals are also suppressed on the data fields, otherwise you'd have subtotals on every field
'          Then the pivot table is filtered to display only "demosrv01" server using the
'          visible property of the PivotItems class

On error resume next
Const xlDatabase = 1 ' Source Type = 1, Microsoft Excel list or database

strDir="C:\Data\"
strAuditSrc="TESTSrc1"
strRptDate = InputBox("Enter report filename date (DD-MMM-YYYY): " & vbCrLf & vbCrLf &_
"The directory is: " & strDir & vbCrLf & _
"The audit source is: " & strAuditSrc , "Pivot Table - TESTSrc1 Report Date", "06-Jan-2007")

xldata = "" & strAuditSrc & "_" & strRptDate & "_analysis" 'existing data worksheet

' Set objExcel = CreateObject("Excel.Application")
Set XL = CreateObject("Excel.Application")

XL.Workbooks.Open ("" & strDir & "" & xldata & ".csv")

XL.Visible = TRUE
XL.ActiveWorkbook.Sheets(xldata).Tab.ColorIndex = 5

xllastcell= xl.cells.specialcells(11).address

XL.Sheets.Add.name = "UpdatingUsers" ' new pivot worksheet
XL.sheets(xldata).select
XL.ActiveSheet.PivotTableWizard xlDatabase,XL.Range("A1" & ":" & xllastcell),"UpdatingUsers!R1C1",xldata
XL.ActiveSheet.PivotTables(xldata).PivotFields("DB Server").Orientation = 1     ' Row field orientation
XL.ActiveSheet.PivotTables(xldata).PivotFields("Manager").Orientation = 1       ' Row field orientation
XL.ActiveSheet.PivotTables(xldata).PivotFields("UserID").Orientation = 1        ' Row field orientation
XL.ActiveSheet.PivotTables(xldata).PivotFields("DB Server").Orientation = 4     ' Data field orientation
XL.ActiveWorkbook.ShowPivotTableFieldList = False
XL.Application.CommandBars("PivotTable").Visible = False

XL.ActiveSheet.PivotTables(xldata).PivotFields("DB Server").Subtotals = _
 Array(False, False, False, False, False, False, False, False, False, False, False, False)
XL.ActiveSheet.PivotTables(xldata).PivotFields("Manager").Subtotals = _
 Array(False, False, False, False, False, False, False, False, False, False, False, False)
XL.ActiveSheet.PivotTables(xldata).PivotFields("UserID").Subtotals = _
 Array(False, False, False, False, False, False, False, False, False, False, False, False)

'** Hides all pivot data items using the server name demosrv01 - this is only an example
XL.ActiveSheet.PivotTables(xldata).PivotFields("DB Server").PivotItems("demosrv01").Visible = False
XL.Cells.EntireColumn.AutoFit

'Subtotal Array elements
' 1st - Automatic
' 2nd - Sum (Adds all the numbers in a range of cells)
' 3rd - Count (Count of like values)
' 4th - Average (Returns the average (arithmetic mean) of the arguments)
' 5th - Max (Returns the largest value in a set of values)
' 6th - Min (Returns the smallest number in a set of values)
' 7th - Product (Multiplies all the numbers given as arguments and returns the product)
' 8th - Count Nums (Counts number of cells that contain numbers)
' 9th - StdDev (Estimates standard deviation based on a sample)
'10th - StdDevp (Calculates standard deviation based on entire population given as arguments)
'11th - Var (Estimates variance based on a sample)
'12th - Varp (Calculates variance based on the entire population)

'Microsoft Excel can automatically calculate subtotal and grand total values in a list. When you insert automatic 
' subtotals, Excel outlines the list so that you can display and hide the detail rows for each subtotal.
' Subtotal an outer row or column field.
'Sum The sum of the values. This is the default function for numeric data. 
'Count The number of data values. The Count summary function works the same as the COUNTA worksheet function. Count 
' is the default function for data other than numbers. 
'Average The average of the values. 
'Max The largest value. 
'Min The smallest value. 
'Product The product of the values. 
'Count Nums The number of data values that are numbers. The Count Nums summary function works the same as the COUNT 
' worksheet function. 
'StDev An estimate of the standard deviation of a population, where the sample is a subset of the entire population. 
'StDevp The standard deviation of a population, where the population is all of the data to be summarized. 
'Var An estimate of the variance of a population, where the sample is a subset of the entire population. 
'Varp The variance of a population, where the population is all of the data to be summarized.