Author Posts

July 7, 2017 at 6:51 pm

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?

July 9, 2017 at 2:01 pm

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.

July 10, 2017 at 5:07 am

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?