Powershell pivot Table creation

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
$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.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)= 'Drive'
$serverInfoSheet.Cells.Item($row,$column).Interior.ColorIndex =48

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

#Now it is time to add the data into the worksheet!
#Increment Row and reset Column back to first column
$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;

    #Increment to next row and reset Column to 1
    $Column = 1

# 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"

$selection = $ws3.Range($range1, $range2)
#Write-Host "R2:"$range2
$PivotTable = $workbook.PivotCaches().Create($xlDatabase,$selection,$xlPivotTableVersion10)
$PivotTable.CreatePivotTable("Pivot!R1C1","Tables1") | Out-Null 
$workbook.ShowPivotTableFieldList = $true 
$PivotFields = $ws3.PivotTables("Tables1").PivotFields("Used Capacity(GB)")
$PivotFields.DragToRow= $false
$PivotFields.Orientation = $xlDataField

$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?

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.


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

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
column which is getting populated in
section which should have been in
columns section
.Can you please help me out with this problem?