Export-Excel Error

[ganapa2000]

Hi,

I am getting the below error while using the export-excel. Please help. I am using Windows Server 2019

Could not open Excel Package D:\reports\VM_Info.xlsx At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.5\Public\Export-Excel.ps1:125 char:16 + catch {throw "Could not open Excel Package $path"} + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (Could not open ...Info.xlsx:String) [], RuntimeException + FullyQualifiedErrorId : Could not open Excel Package D:\reports\VM_Info.xlsx

[LucD]

Which version of the ImportExcel module are you using?

[ganapa2000]

Hi LucD,

Please find the version details.

I am using the below script to get the data from multiple vcenters, some of the below folders dont exists in one of the vcenters. The below script was working fine without any issues, suddenly, I am seeing above export-excel errors.

If it folders are from one vcenter and they exists, I dont see these errors. but when I use multiple vcenter, I am getting export-excel error

#Connect to VIServer
$vCenters = @(
"MyVC1"
"MyVC2"
)
Connect-viserver -Server $vCenters

#Disk Space Report
$vmfreeInfo = Foreach ($vc in $global:DefaultVIServers)
{
ForEach ($VM in get-folder 'Folder1', 'Dev1', 'Training', "Dev2" -Server $vc -ErrorAction silentlycontinue | Get-VM -Server $vc)
{
($VM.Extensiondata.Guest.Disk | Select @{N='vCenter';E={([uri]$VM.ExtensionData.Client.ServiceUrl).Host}},
@{N="Name";E={$VM.Name}},
@{N="IP Address";E={$VM.guest.IPAddress[0]}},
@{N="VM PowerState";E={@($VM.PowerState)}},
@{N="OS"; E={@($VM.guest.OSFullName)}},
@{N="Folder";E={$VM.Folder.Name}},
@{N="MountPoint";E={$_.DiskPath}},
@{N="Cluster";E={$VM.VMHost.Parent}},
@{N="Capacity(GB)";E={[math]::Round($_.Capacity/ 1GB)}},
@{N="FreeSpace(GB)";E={[math]::Round($_.FreeSpace / 1GB)}},
@{N="FreeSpace(%)";E={[math]::Round(((100* ($_.FreeSpace))/($_.Capacity)),0)}},
@{N="UsedSpace(GB)";E={[math]::Round(((($_.Capacity / 1GB))-($_.FreeSpace / 1GB)),0)}})
}
}
$vmfreeInfo | | Export-Excel -Path "D:\Myreport.xlsx"

[LucD]

I'm not sure where the folders come into play in that script.
The results for all vCenters are collected in one array, and then that array is saved to an XLSX file.

[wetnose88]

My code to process multiple vCenters are as follows:

$vcenters = "mytest01.homelab.ca","mytest02.homelab.ca","mytest03.homelab.ca","mytest04.homelab.ca"
$creds = import-clixml -Path "C:\Docs\Scripts\Credentials\Cred_jzadmin.xml"

$outputfile = "$env:USERPROFILE\Outputs\VmOsDriveFreeSpace\VMDiskSystemDriveFreeSpace_$(get-date -Format yyyy-MM-dd-HH).xlsx"

$stopwatch = New-Object System.Diagnostics.Stopwatch
$stopwatch.Start()

Foreach ($vcenter in $vcenters){
$report = @()
Write-Host "Connectint to $vCenter ......" -ForegroundColor Cyan
Connect-VIServer -Server $vcenter -Credential $Creds

......
Write-Host "Disconnect from $vCenter " -ForegroundColor Yellow
Disconnect-viserver * -confirm:$false
$report | Export-Excel "$outputfile " -WorkSheetname "$($vcenter)"
}

$stopwatch.Stop()
$stopWatch

write-host "Done!"
Write-Host "Please review the reports under $outputfile"
invoke-item $outputfile