1: # Create $cred variable (only if you need to pass different creds)
2: $cred = Get-Credential 'osu\fatbeard'
3: Connect-VIServer -Server 'VM-Server' -Credential $cred
4:
5: # Get all VM Boxes, we are interested in this
6: # point only in the name and PowerState
7: $VirtualServers = Get-VM | sort Name | select Name, PowerState
8:
9: # Create an instance of Excel
10: $Excel = New-Object -comobject Excel.Application
11: $Excel.visible = $True
12: $Workbook = $Excel.Workbooks.Add()
13: $Info = $Workbook.Worksheets.Item(1)
14:
15: # Create our column headers
16: $Info.Cells.Item(1,1) = "VM Name"
17: $Info.Cells.Item(1,2) = "PowerState"
18:
19: # Add a little formatting to the column header
20: $Style = $Info.UsedRange
21: $Style.Interior.ColorIndex = 19
22: $Style.Font.ColorIndex = 11
23: $Style.Font.Bold = $True
24:
25: # Data starts at row 2
26: $intRow = 2
27:
28: # iterate over each VM
29: foreach ($VirtualServer in $VirtualServers)
30: { 31: if ($VirtualServer.PowerState -eq "PoweredOn")
32: { 33: $PoweredState = 1
34: }
35: else
36: { 37: $PoweredState = 0
38: }
39:
40: $Style.Cells.Item($intRow, 1) = $VirtualServer.Name
41: $Style.Cells.Item($intRow, 2) = $PoweredState
42: $intRow++
43: }
44:
45: ## Now for some formatting
46: # For more data on the enumeration values look at the following:
47: # XlFormatConditionType Enumeration: http://msdn.microsoft.com/en-us/library/bb241301.aspx
48: # XlFormatConditionOperator Enumeration: http://msdn.microsoft.com/en-us/library/bb241299.aspx
49: # XlIconSet Enumeration http://msdn.microsoft.com/en-us/library/bb241324.aspx
50: # XlConditionValueTypes Enumeration http://msdn.microsoft.com/en-us/library/bb241028.aspx
51: ## The constants that we will use
52:
53: $xlCenter = -4108
54: $xlCellValue = 1
55: $xlEqual = 3
56: $xl3TrafficLights2 = 5
57: $xl3Symbols = 7
58: $xlConditionValueNumber = 0
59: $xlIconSet = 6
60:
61: # Make Column "A" wide enough for content
62: $info.Range("A:A").entireColumn.Autofit() 63:
64: # Make Column "B" wide enough for content and Center the contents
65: $info.Range("B:B").entireColumn.Autofit() 66: $info.Range("B:B").HorizontalAlignment = $xlCenter 67:
68: # This will fill the cell
69: #$Info.Range("B:B").FormatConditions.Add($xlCellValue,$xlEqual,'="Y"') 70: #$Info.Range("B:B").FormatConditions.Item(1).Interior.Color = 5296274 71:
72: $Info.Range("B:B").FormatConditions.Add($xlIconSet) 73: $Info.Range("B:B").FormatConditions.AddIconSetCondition 74: $Info.Range("B:B").FormatConditions.Item(1).ShowIconOnly = $true 75: $Info.Range("B:B").FormatConditions.Item(1).IconSet = $Excel.ActiveWorkbook.IconSets.Item($xl3Symbols) 76: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(2).Value = 0 77: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(2).Operator = 5 78: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(3).Value = 1 79: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(3).Operator = 7 80:
81:
82:
83:
