Hi!
After a long time I decided to change the script to stop using Excel COM objects and start using ImportExcel module.
Since my company use only ESXi free hosts, I had to create a way to get all information from those servers daily.
Some parts of this worksheet are added manually because the free version doesn't allow to extract that information. (Ex.: Physical server info used on comments)
I know that it won't be something that a lot of people will need, but I used it to learn more about PowerShell, and it's always fun to do something different, right? 
PS: I still don't know how to make the script faster... LucD gave me some tips but I just don't know a way how to do it... I'll have to study more to know how to make the script better.
PS2: The script was written using brazilian portuguese and I had to translate it to post here, sorry if there are some typos in there...
Here it goes a sample.
Screenshot of how the worksheet will be like:
Comment with the physical host information:
Comment showing notes for every VM:
Comment showing VMware Tools status:
$HVVMWARE = [System.Collections.ArrayList]@(
"Server Name"
"Server Name 2"
"Server Name 3"
"Server Name 4"
)
$File = "C:\PowerShell\VMware\Files\ConfigVirtual.xlsx"
$InactiveServers = @()
#Function to retrieve physical servers info
function HostInfo {
$TotalGB = $Null
$Datastores = Get-VMHost $ESXiServer.Name | Get-Datastore
foreach ($Datastore in $Datastores) {
$TotalGB += $Datastore.CapacityGB
}
$WorkSheet.Cells.Item(7,2).Value = $ESXiServer.NumCpu
$WorkSheet.Cells.Item(7,3).Value = ($ESXiServer | Select-Object @{N = "RAM"; E = { [math]::Round($_.MemoryTotalGB, 0) } }).RAM
$WorkSheet.Cells.Item(7,4).Value = [math]::Round($TotalGB, 0)
$WorkSheet.Cells.Item(7,6).Value = "VMware " + ($ESXiServer).Version + " build " + ($ESXiServer).Build
$WorkSheet.Cells.Item(7,7).Value = if ($ESXiServer.PowerState -eq 1) { "On" } else { "Off" }
}
#Function to retrieve physical servers resources
function Resources {
$ActiveServers = Get-VM -Server $ESXiServer.Name | Where-Object { $_.PowerState -eq "PoweredOn" }
$UsedCPU = $Null
$UsedRAM = $Null
$TotalFreeGB = $Null
foreach ($CPU in $ActiveServers.NumCpu) {
$UsedCPU += $CPU
}
foreach ($RAM in $ActiveServers.MemoryGB) {
$UsedRAM += $RAM
}
$ServerRAM = ($ESXiServer | Select-Object @{N = "RAM"; E = { [math]::Round($_.MemoryTotalGB, 0) } }).RAM
$TotalCPU = $ESXiServer.NumCpu - $UsedCPU
$Datastores = Get-VMHost $ESXiServer.Name | Get-Datastore
foreach ($Datastore in $Datastores) {
$TotalFreeGB += $Datastore.FreeSpaceGB
}
$WorkSheet.Cells.Item(8,2).Value = $TotalCPU
$WorkSheet.Cells.Item(8,3).Value = [math]::Round(($ServerRAM - $UsedRAM), 0)
$WorkSheet.Cells.Item(8,4).Value = [math]::Round($TotalFreeGB, 0)
}
#Function to retrieve VMs info
function ServerInfo {
$i = 1
foreach ($Server in $Servers) {
Write-Progress -Id 0 -Activity "Host: $($Server.VMHost.Name)"
Write-Progress -Id 1 -ParentId 0 -Activity "Verifying VMs..." -CurrentOperation $Server -Status "Status: $([math]::round(($i / $Servers.count)*100))%" -PercentComplete (($i / $Servers.count) * 100) | Out-Default
$i++
$TotalHD = $Null
$HardDisks = (Get-HardDisk $Server).CapacityGB
foreach ($HardDisk in $HardDisks) {
$TotalHD += $HardDisk
}
if ($Server.Notes) {$Notes = $Server.Notes} else {$Notes = "Server without notes, please fix it on VMware."}
$WorkSheet = $WorkBook01.Item($ESXiServer)
$WorkSheet.Cells["A$Line"].Value = $Server.Name
$WorkSheet.Cells["A$Line"].AddComment($Notes,"VMware") | Out-Null
$Font = $WorkSheet.Cells["A$Line"].Comment
$Font.Font.Bold = $true
$Font.Font.Size = 9
$Font.Font.FontName = "Segoe UI"
$WorkSheet.Cells["A$Line"].Comment.AutoFit = $true
$WorkBook01.Item($ESXiServer).Cells.Item($Line,2).Value = $Server.NumCpu
$WorkBook01.Item($ESXiServer).Cells.Item($Line,3).Value = [math]::Round($Server.MemoryGB)
$WorkBook01.Item($ESXiServer).Cells.Item($Line,4).Value = [math]::Round($TotalHD, 0)
$WorkBook01.Item($ESXiServer).Cells.Item($Line,5).Value = if ((Get-Datastore -VM $Server.name).count -gt 1) {
$Datastores = Get-Datastore -VM $Server.name | Select-Object -ExpandProperty Name
$Datastores -join ", "
}
else {
(Get-Datastore -VM $Server.name).Name
}
$WorkBook01.Item($ESXiServer).Cells.Item($Line,6).Value = $Server.Guest.OSFullName
$WorkBook01.Item($ESXiServer).Cells.Item($Line,7).Value = if ($Server.PowerState -eq 1) { "On" } else { "Off" }
$WorkBook01.Item($ESXiServer).Cells.Item($Line,8).Value = $Server.HardwareVersion.ToString()
$WorkBook01.Item($ESXiServer).Cells.Item($Line,9).Value = if (((($Server.Guest.ExtensionData).ToolsStatus.ToString()) -eq "toolsNotRunning") -or ((($Server.Guest.ExtensionData).ToolsStatus.ToString()) -eq "toolsOld")) {
1
}
elseif ((($Server.Guest.ExtensionData).ToolsStatus.ToString()) -eq "toolsOk") {
2 }
else {
0
}
if (($Server.Guest.ExtensionData).ToolsStatus.ToString() -ne "toolsOk") {
$WorkSheet.Cells["I$Line"].AddComment(($Server.Guest.ExtensionData).ToolsStatus.ToString(),"VMware") | Out-Null
$Font = $WorkSheet.Cells["I$Line"].Comment
$Font.Font.Bold = $true
$Font.Font.Size = 9
$Font.Font.FontName = "Segoe UI"
$WorkSheet.Cells["I$Line"].Comment.AutoFit = $true
}
$WorkBook01.Item($ESXiServer).Cells.Item($Line,10).Value = if (Get-VM $Server | Get-Snapshot) {
"Yes"
}
else {
"No"
}
$Line = $Line + 1
}
}
$ESXiServers = Get-VMHost | Sort-Object Name
$Excel01 = Open-ExcelPackage -Path "C:\PowerShell\VMware\Files\ConfigVirtual-Original.xlsx"
$WorkBook01 = $Excel01.Workbook.Worksheets
$InactiveServers = Compare-Object -ReferenceObject $HVVMWARE -DifferenceObject $ESXiServers.Name | Select-Object -ExpandProperty InputObject
foreach ($InactiveServer in $InactiveServers){
$WorkBook01.Item($InactiveServer).Cells.Item(7,7).Value = "Off"
}
$i = 1
foreach ($ESXiServer in $ESXiServers) {
Write-Progress -Activity "Verifying servers..." -CurrentOperation $ESXiServer -Status "Status: $([math]::round(($i / $ESXiServers.count)*100))%" -PercentComplete (($i / $ESXiServers.count) * 100) | Out-Default
$i++
$WorkSheet = $WorkBook01.Item($ESXiServer.Name)
HostInfo
Resources
}
Write-Progress -Activity "Verifying servers..." -Completed | Out-Default
foreach ($ESXiServer in $ESXiServers.Name) {
$Line = 9
$Servers = (Get-VM -Server $ESXiServer | Where-Object { $_.Name -notlike "Unknown*" })
ServerInfo
}
Write-Progress -Id 1 -ParentId 0 -Activity "Verifying VMs..." -Completed | Out-Default
Write-Progress -Id 0 -Completed -Activity "Host: $($Server.VMHost.Name)" | Out-Default
Close-ExcelPackage $Excel01 -SaveAs $File
$Excel01 = $Null