I have seen on many if the VMware followers forums a way to use Excel to send commands to the VI server to create machines, alter vlans etc.
I wanted a way to export information and could not find anyone doing this yet other than using the export-csv so I created the following script which will export all the information for all Virtual machines in your virtual center straight to excel ready for you to manipulate.
Don't forget to change yourservername for your virtual center server.
Get-VIServer yourservername
$Excel = New-Object -Com Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add(1)
$Sheet = $Excel.WorkSheets.Item(1)
$Sheet.Cells.Item(1,1) = "Name"
$Sheet.Cells.Item(1,2) = "Power State"
$Sheet.Cells.Item(1,3) = "Description"
$Sheet.Cells.Item(1,4) = "Number of CPUs"
$Sheet.Cells.Item(1,5) = "Memory (MB)"
$WorkBook = $Sheet.UsedRange
$WorkBook.Font.Bold = $True
$intRow = 2
$colItems = Get-VM | Select-Object -property "Name","PowerState","Description","NumCPU","MemoryMB"
foreach ($objItem in $colItems) {
$Sheet.Cells.Item($intRow,1) = $objItem.Name
$powerstate = $objItem.PowerState
If ($PowerState -eq 1) {$power = "Powerd On"}
Else {$power = "Powerd Off"}
$Sheet.Cells.Item($intRow,2) = $power
$Sheet.Cells.Item($intRow,3) = $objItem.Description
$Sheet.Cells.Item($intRow,4) = $objItem.NumCPU
$Sheet.Cells.Item($intRow,5) = $objItem.MemoryMB
$intRow = $intRow + 1
}
$WorkBook.EntireColumn.AutoFit()
Clear
Here’s a more modern and feature-rich way of doing this.
https://github.com/dfinke/ImportExcel
I am a complete noob to Vmware Powershell Scripting. Can someone please teach me, step by step, how to implement the above script.
Thanks in Advance.