Error while exporting

[ganapa2000]

Hi,

I am getting the below error while I run the below script, please help.

Failed exporting worksheet '' to 'D:\reports\West_DR_VM_Infra_Report_18-Sep-2018.xlsx': Cannot bind argument to parameter 'WorkSheetname' because it is an empty string.

At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\Export-Excel.ps1:604 char:17

+                 throw "Failed exporting worksheet '$WorkSheetname' to '$Path': $ ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : OperationStopped: (Failed exportin...n empty string.:String) [], RuntimeException

    + FullyQualifiedErrorId : Failed exporting worksheet '' to 'D:\reports\West_DR_VM_Infra_Report_18-Sep-2018.xlsx': Cannot bind argument to parameter 'WorkSheetname' because it is an empty stri

   ng.

Script is

function get-mysummary()

{

  $report = Get-Folder | Get-VM | Select Folder, Name,

  @{N="IP Address";E={@($_.guest.IPAddress[0])}},

  @{N="CPU";E={@($_.NumCPU)}},

  @{N="Memory (GB)";E={@($_.MemoryGB)}},

  @{N="Provisioned (GB)";E={[math]::Round($_.provisionedspacegb)}}

  $sum = $report | Measure-Object -sum "CPU", "Memory (GB)", "Provisioned (GB)"

  $row1 = "" | Select "Folder", "CPU","Memory (GB)","Provisioned (GB)"

  $report += $row1

  $row2 = "" | Select "Folder", "CPU","Memory (GB)","Provisioned (GB)"

  $row2."CPU" = 'Total CPU'

  $row2."Memory (GB)" = 'Total Memory (GB)'

  $row2."Provisioned (GB)" = 'Total Provisioned(GB)'

  $report += $row2

  $row3 = "" | Select "Folder", "CPU","Memory (GB)","Provisioned (GB)"

  $row3."Folder" = 'Total'

  $row3.CPU = $sum | where{$_.Property -eq 'CPU'} | select -ExpandProperty Sum

  $row3."Memory (GB)" = $sum | where{$_.Property -eq "Memory (GB)"} | select -ExpandProperty Sum

  $row3."Provisioned (GB)" = $sum | where{$_.Property -eq "Provisioned (GB)"} | select -ExpandProperty Sum

  $report += $row3

  $report

}

#Parameters

$date = Get-Date -format dd-MMM-yyyy

$reportlocation = "D:\reports\West_DR_VM_Infra_Report_$date.xlsx"

get-mysummary | Group-Object -Property Folder | %{

    $folderName = $_.Folder.Name

    $_.Group | Export-Excel -Path $reportlocation -AutoFilter -AutoSize -WorksheetName $folderName

}

[LucD]

The Group-Object cmdlet produces an object with 3 properties: the Count, the Name and the Group property.

The Name property contains the value of the property on which you grouped.

Your last lines should be

Get-MySummary | Group-Object -Property Folder | %{

    $folderName = $_.Name

    $_.Group | Export-Excel -Path $reportlocation -AutoFilter -AutoSize -WorksheetName $folderName

}

[LucD]

Also watch out, when you function returns a line where the Folder property is blank, there will be a group where the Name is blank.

And you will get the same error.

[ganapa2000]

Hi LucD,

Total of CPU, Memory and Harddisk is not getting generated. there are no VMs which are outside any folders.

Failed exporting worksheet '' to 'D:\reports\West_DR_VM_Infra_Report_18-Sep-2018.xlsx': Cannot bind argument to parameter 'WorkSheetname' because it is an empty string.

At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\Export-Excel.ps1:604 char:17

+                 throw "Failed exporting worksheet '$WorkSheetname' to '$Path': $ ...

+                 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : OperationStopped: (Failed exportin...n empty string.:String) [], RuntimeException

    + FullyQualifiedErrorId : Failed exporting worksheet '' to 'D:\reports\West_DR_VM_Infra_Report_18-Sep-2018.xlsx': Cannot bind argument to parameter 'WorkSheetname' because it is an empty stri

   ng.

[LucD]

With $row1 and $row2 in your function, you are creating an entry where the Folder is empty.

Then when you group on Folder, there will be 1 group where Name is empty.

And the Export-Excel cmdlet doesn't like it when the WorksheetName value is $null

[ganapa2000]

LucD,

Total of CPU, Memory and Harddisk is not getting generated. I am just seeing the list without Total ?

please help

[LucD · Accepted solution]

Why don't you do it like this?
It doesn't require the Group-Object cmdlet.

function get-mysummary() {

  param(

   [VMware.VimAutomation.ViCore.Types.V1.Inventory.Folder]$Folder

  )

  $report = @()

  $report += Get-VM -Location $Folder |

  Select Folder, Name,

  @{N = "IP Address"; E = {@($_.guest.IPAddress[0])}},

  @{N = "CPU"; E = {@($_.NumCPU)}},

  @{N = "Memory (GB)"; E = {@($_.MemoryGB)}},

  @{N = "Provisioned (GB)"; E = {[math]::Round($_.provisionedspacegb)}}

  $sum = $report | Measure-Object -sum "CPU", "Memory (GB)", "Provisioned (GB)"

  $row1 = "" | Select "Folder", "CPU", "Memory (GB)", "Provisioned (GB)"

  $report += $row1

  $row2 = "" | Select "Folder", "CPU", "Memory (GB)", "Provisioned (GB)"

  $row2."CPU" = 'Total CPU'

  $row2."Memory (GB)" = 'Total Memory (GB)'

  $row2."Provisioned (GB)" = 'Total Provisioned(GB)'

  $report += $row2

  $row3 = "" | Select "Folder", "CPU", "Memory (GB)", "Provisioned (GB)"

  $row3."Folder" = 'Total'

  $row3.CPU = $sum | where {$_.Property -eq 'CPU'} | select -ExpandProperty Sum

  $row3."Memory (GB)" = $sum | where {$_.Property -eq "Memory (GB)"} | select -ExpandProperty Sum

  $row3."Provisioned (GB)" = $sum | where {$_.Property -eq "Provisioned (GB)"} | select -ExpandProperty Sum

  $report += $row3

  $report

}

#Parameters

$date = Get-Date -format dd-MMM-yyyy

$reportlocation = "D:\reports\West_DR_VM_Infra_Report_$date.xlsx"

$reportlocation = "C:\Temp\West_DR_VM_Infra_Report_$date.xlsx"

foreach ($fld in Get-Folder -Type VM) {

  get-mysummary -Folder $fld |

   Export-Excel -Path $reportlocation -AutoFilter -AutoSize -WorksheetName $fld.Name

}

[ganapa2000]

Thank you very much LucD, you are simply superb as always