Powershell pivot Table creation

PowerShell Help
1

Hi Guys,

I am facing issue with the pivot table creation through powershell. Below is the code that I am using

# requires excell COM 
#Create excel COM object
Clear-Host
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True

#Add a workbook
$workbook = $excel.Workbooks.Add()


#Connect to first worksheet to rename and make active
$pivotSheet=$workbook.Worksheets.Add();
$pivotSheet=$workbook.Worksheets.Item("Sheet1")
$pivotSheet.Name ="Pivot"
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Name = 'DiskInformation'
$serverInfoSheet.Activate() | Out-Null


#Create a Title for the first worksheet and adjust the font
$row = 1
$Column = 1

#Create a header for Disk Space Report; set each cell to Bold and add a background color
$serverInfoSheet.Cells.Item($row,$column)= 'Environment'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'Drive'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++

$serverInfoSheet.Cells.Item($row,$column)= 'TotalCapacity(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'Used Capacity(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'Free Space(GB)'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++
$serverInfoSheet.Cells.Item($row,$column)= 'Freespace %'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48
$serverInfoSheet.Cells.Item($row,$column).Font.Bold=$True
$Column++

#Now it is time to add the data into the worksheet!
#Increment Row and reset Column back to first column
$row++
$Column = 1
$disks = Get-WmiObject -ComputerName "sanky555" -Class Win32_LogicalDisk -Filter "DriveType = 3" 


foreach($disk in $disks) 
{         
        $deviceID = $disk.DeviceID; 
        $volName = $disk.VolumeName; 
        [float]$size = $disk.Size; 
        [float]$freespace = $disk.FreeSpace;  
        $percentFree = [Math]::Round(($freespace / $size) * 100, 2); 
        $sizeGB = [Math]::Round($size / 1073741824, 2); 
        $freeSpaceGB = [Math]::Round($freespace / 1073741824, 2); 
        $usedSpaceGB = $sizeGB - $freeSpaceGB; 
        $color = $whiteColor; 
        Write-Host $deviceID $volName $size $freespace $percentFree $freeSpaceGB $usedSpaceGB 

        $serverInfoSheet.Cells.Item($row,$column)= "Test";$Column+=1;
        $serverInfoSheet.Cells.Item($row,$column)= $deviceID;$Column+=1;
        $serverInfoSheet.Cells.Item($row,$column)= $sizeGB;$Column+=1;
        $serverInfoSheet.Cells.Item($row,$column)= $usedSpaceGB;$Column+=1;
        $serverInfoSheet.Cells.Item($row,$column)= $freeSpaceGB;$Column+=1;
        $serverInfoSheet.Cells.Item($row,$column)= $percentFree;$Column+=1;
        

    $Column=1;
    $row++
}
    #Increment to next row and reset Column to 1
    $Column = 1
    $row++



# rename workbook
#$workbook = $workbook
#$workbook = $excel.Worksheets.add()

# Get sheets
$ws3 = $workbook.worksheets | where {Write-Host "Sheet Name;"$_.name} #<------- Selects sheet 3
$ws3 = $workbook.worksheets | where {$_.name -eq "DiskInformation"} #<------- Selects sheet 3


$xlPivotTableVersion12     = 3
$xlPivotTableVersion10     = 1
$xlCount                 = -4112
$xlDescending             = 2
$xlDatabase                = 1
$xlHidden                  = 0
$xlRowField                = 1
$xlColumnField             = 2
$xlPageField               = 3
$xlDataField               = 4   
$xlDirection        = [Microsoft.Office.Interop.Excel.XLDirection]
# R1C1 means Row 1 Column 1 or "A1"
# R65536C5 means Row 65536 Column E or "E65536"

$range1=$ws3.range("A1")
$range1=$ws3.Range($range1,$range1.End($xlDirection::xlDown))
$range2=$ws3.range("H1")
$range2=$ws3.Range($range2,$range2.End($xlDirection::xlDown))
$selection = $ws3.Range($range1, $range2)
#Write-Host "R2:"$range2
$PivotTable = $workbook.PivotCaches().Create($xlDatabase,$selection,$xlPivotTableVersion10)
$PivotTable.CreatePivotTable("Pivot!R1C1","Tables1") | Out-Null 
[void]$ws3.select();
$workbook.ShowPivotTableFieldList = $true 
$PivotFields = $ws3.PivotTables("Tables1").PivotFields("Used Capacity(GB)")
$PivotFields.DragToRow= $false
#$PivotFields.IncludeNewItemsInFilter=$true;
$PivotFields.Orientation = $xlDataField

#$PivotFields.Position=1
$PivotFields = $ws3.PivotTables("Tables1").PivotFields("Farms/Classification")
$PivotFields.Orientation = $xlRowField
$PivotFields = $ws3.PivotTables("Tables1").PivotFields("Environment")
$PivotFields.Orientation = $xlColumnField
$PivotFields = $ws3.PivotTables("Tables1").PivotFields("Free Space(GB)")
$PivotFields.DragToRow= $false
$PivotFields.Orientation = $xlDataField

Below is the error that I receive.

Unable to get the PivotTables property of the Worksheet class
for the line 
$PivotFields = $ws3.PivotTables("Tables1").PivotFields("Used Capacity(GB)")
. Suprisingly this issue happens to be only when I try to create a pivot table in the new excel sheet. can you please help me to identify the issue here?
2

Magazines have “issues;” you have a “problem.” Part of that problem is that the Excel programmability components are ancient and COM-based, and sometimes get weird in .NET - and thus, in PowerShell. You might consider posting your question on StackOverflow - it gets a larger developer audience, and what you’re dealing with isn’t really a PowerShell problem. It’s pure COM and .NET, despite the fact that you’re accessing it from within PowerShell. The audience over there likely has more experience with this kind of thing.

3

Hi,

Thank you for the suggestion, but I have resolved it on my own. I just had to replace 

ws3
with 
pivotSheet
variable in-order to change the reference. But now it seems like I am facing a new problem. Summation of two coumns is automatically generating a 
values
column which is getting populated in 
rows
section which should have been in 
columns section
.Can you please help me out with this problem?