Incrementing variable names (PowerCLI - Servers information)

PowerShell Help
1

Hi,

I’m trying to create an Excel file to populate VMware servers informations (using PowerCLI).
The problem I’m having is that I want to create an Excel file with “X” worksheets (where X should be the number of physical servers) and then rename them to each server.
What I have so far:

$ESXiServers = Get-VMHost | Sort Name

$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $True
$Excel01.SheetsInNewWorkBook = $ESXiServers.Count
$WorkBook01 = $Excel01.Workbooks.Add()

$i = 1
foreach ($item in $ESXiServers) {
New-Variable Worksheet$i;$i++;$i -eq 15
}

$variables = (get-variable | where {$_.name -like "Worksheet*"}).name

$i = 1
$j = 0
foreach ($variable in $variables) {
$global:variable = $Workbook01.Sheets.Item($i)
$variable.Name = $ESXiServers[$j].Name
$i++
$j++
}

The problem is that in this way, the Excel file is created 15 worksheets are created and correctly renamed, but to fill the cells later I need that “Worksheet#number” variables to exist.

I was thinking about ignoring the first foreach and the code to fill the $variables and use something like:

While ($i -le 15) {
$global:Worksheet$i  = $Workbook01.Sheets.Item($i)
$Worksheet$i.Name = $ESXiServers[$j].Name
}

But everything I’ve tried to increment the $Worksheet variable has bad results…

I’m lost…
Any help?

2

Anytime you think you want to increment a variable name like that, it means what you probably wanted was a collection. :slight_smile: I’m not sure exactly what your code is trying to do, but instead of $worksheet$i and New-Variable, you would instead reference something like $worksheets[$i], where $worksheets would be an array or other collection that you build up previously.

If you really do want to use New-Variable with dynamically-generated names for some reason, then later on, you’ll need to use Get-Variable in order to retrieve those values. “$worksheet$i” doesn’t work; $worksheet and $i are treated as two separate variables. However, this would work: Get-Variable -Name “Worksheet$i” -ValueOnly

3

Hey Dave,

Thanks for your really fast reply.

My code is just trying to create this variables:

$Worksheet1
$Worksheet2
$Worksheet3
$Worksheet4
$Worksheet5
$Worksheet6
$Worksheet7
$Worksheet8
$Worksheet9
$Worksheet10
$Worksheet11
$Worksheet12
$Worksheet13
$Worksheet14
$Worksheet15

So I can add them this code:

$Worksheet1 = $Workbook01.Sheets.Item(1)
$Worksheet2 = $Workbook01.Sheets.Item(2)
$Worksheet3 = $Workbook01.Sheets.Item(3)
etc...

I need them to fill and format the cells after using:

$Worksheet01.Activate()
$Worksheet01.Cells.Item(1,1) = "Server name"
$Worksheet01.Cells.Item(1,1).Font.Bold = $True

Can you give me an idea of how I could make it using collections(arrays)?

4

I don’t see a reason why you need to do that at all. The only thing that really changes in your code is the number passed to .Item. For example:

for ($i = 1; $i -lt 15; $i++)
{
    $worksheet = $workbook01.Sheets.Item($i)
    $worksheet.Activate()
    $worksheet.Cells.Item(1,1) = "Server Name"
    $worksheet.Cells.Item(1,1).Font.Bold = $true
}

There’s no advantage, in this code, to naming the $worksheet01 versus $worksheet (or to having an array of $worksheets, either; you already have that, in the form of the .Item() collection on $workbook01.Sheets). Since it’s in a loop, it doesn’t really matter.

5

Dave Wyatt,

Thanks a lot =]

I managed to make it what I want this way:

function ServerInfo {
foreach ($Server in $Servers) {
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,1) = $Server.Name
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,2) = $Server.NumCpu
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,3) = $Server.MemoryGB
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,4) = (Get-Datastore -VM $Server.name).name
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,5) = $Server.Guest.OSFullName
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,6) = $Server.PowerState
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,7) = $Server.Version
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,8) = ($Server.Guest.ExtensionData).ToolsStatus
($WorkBook01.Sheets.Item($ESXiServer)).Cells.Item($Row,9) = $Server.Guest.ToolsVersion
($WorkBook01.Sheets.Item($ESXiServer)).columns.item("A:I").EntireColumn.AutoFit() | Out-Null
$Row = $Row + 1
}
}

$ESXiServers = Get-VMHost | Sort Name

$Excel01 = New-Object -ComObject Excel.Application
$Excel01.Visible = $False
$Excel01.SheetsInNewWorkBook = $ESXiServers.Count
$WorkBook01 = $Excel01.Workbooks.Add()

for ($i = 1; $i -le ($ESXiServers.Count); $i++)
{
$WorkSheet = $WorkBook01.Sheets.Item($i)
$Worksheet.Activate()
$WorkSheet.Name = $ESXiServers[$i-1].Name
$worksheet.Cells.Item(1,1) = "Server"
$Worksheet.Cells.Item(1,2) = "CPUs"
$Worksheet.Cells.Item(1,3) = "RAM"
$Worksheet.Cells.Item(1,4) = "Volume"
$Worksheet.Cells.Item(1,5) = "OS"
$Worksheet.Cells.Item(1,6) = "VM state"
$Worksheet.Cells.Item(1,7) = "VM version"
$Worksheet.Cells.Item(1,8) = "VMware tools installed?"
$Worksheet.Cells.Item(1,9) = "VMware tools version"
$Worksheet.Range("A1:I1").Font.Bold = $True
$Worksheet.Range("A1:I1").Borders.LineStyle = 1
}

foreach ($ESXiServer in $ESXiServers.Name) {
$Row = 2
$Servers = (Get-VM -Server $ESXiServer)
ServerInfo
}

$Workbook01.SaveAs("C:\VMware\Files\VirtualConfig.xlsx")
$Excel01.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel01) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Workbook01) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Worksheet) | Out-Null
$Excel01 = $Null

I’ll have to make it better and add other things… But you helped a lot!
Thanks once again