Incrementing variable names (PowerCLI - Servers information)

This topic contains 4 replies, has 2 voices, and was last updated by  Vandrey Trindade 3 years, 2 months ago.

  • Author
  • #23653

    Vandrey Trindade


    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 {$ -like "Worksheet*"}).name
    $i = 1
    $j = 0
    foreach ($variable in $variables) {
    $global:variable = $Workbook01.Sheets.Item($i)
    $variable.Name = $ESXiServers[$j].Name

    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?

  • #23654

    Dave Wyatt

    Anytime you think you want to increment a variable name like that, it means what you probably wanted was a collection. 🙂 I'm not sure exactly what your code is trying to do, but instead of [b]$worksheet$i[/b] and New-Variable, you would instead reference something like [b]$worksheets[$i][/b], 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

  • #23656

    Vandrey Trindade

    Hey Dave,

    Thanks for your really fast reply.

    My code is just trying to create this variables:


    So I can add them this code:

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

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

    $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)?

  • #23657

    Dave Wyatt

    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.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.

  • #23696

    Vandrey Trindade

    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 $
    ($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.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)
    [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

You must be logged in to reply to this topic.