Excel Data into Combo-Box

PowerShell Help
1

Hi Forum,
I am trying to populate Data out of an Excel worksheet into an Combo-Box.
In the script itself, the responsible variable “$objCombobox.Items” helds all correct data

But in the Combo-Box, i unfortunately only get shown several “system object” entries…

This is how the correspondent excel sheet is looking like:
image

and this is the combo-box, I get
image

Where is my mistake?

Here is my code

type or paste code here

[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms")
[void] [System.Reflection.Assembly]::LoadWithPartialName("System.Drawing") 
$Namen = @()
$Zeile=1
$Page = "User"

$objForm = New-Object System.Windows.Forms.Form 
$objForm.Text = "Combobox"
$objForm.Size = New-Object System.Drawing.Size(300,200) 
$objForm.StartPosition = "CenterScreen"
$objForm.KeyPreview = $True

$OKButton = New-Object System.Windows.Forms.Button
$OKButton.Location = New-Object System.Drawing.Size(75,120)
$OKButton.Size = New-Object System.Drawing.Size(75,23)
$OKButton.Text = "OK"
$OKButton.Add_Click(
   {
 foreach ($objItem in $objCombobox.SelectedItem)
         {$global:x = $objItem
          $global:x}
         $objForm.Close()
       })
$objForm.Controls.Add($OKButton)

$CancelButton = New-Object System.Windows.Forms.Button
$CancelButton.Location = New-Object System.Drawing.Size(150,120)
$CancelButton.Size = New-Object System.Drawing.Size(75,23)
$CancelButton.Text = "Cancel"
$CancelButton.Add_Click({$objForm.Close()})
$objForm.Controls.Add($CancelButton)

$objLabel = New-Object System.Windows.Forms.Label
$objLabel.Location = New-Object System.Drawing.Size(10,20) 
$objLabel.Size = New-Object System.Drawing.Size(280,20) 
$objLabel.Text = "Treffen Sie bitte eine Auswahl:"
$objForm.Controls.Add($objLabel) 

$objCombobox = New-Object System.Windows.Forms.Combobox 
$objCombobox.Location = New-Object System.Drawing.Size(10,40) 
$objCombobox.Size = New-Object System.Drawing.Size(260,20) 
$objCombobox.Height = 70

$objexcel=New-Object -ComObject Excel.Application
$workbook=$objexcel.WorkBooks.Open('C:\temp\Users.xlsx')
$worksheet=$workbook.WorkSheets.item($Page)
$objexcel.Visible= $false


do {
      $Namen = @($worksheet.Cells.Item($Zeile,1).Text)
      write-host Name = $Namen
      [void] $objCombobox.Items.Add($Namen)
      $Zeile++
       }
while($worksheet.Cells.Item($Zeile,1).Text.Length -gt 0)

$objexcel.quit()
$objForm.Controls.Add($objCombobox) 

$objForm.Topmost = $True
$objForm.Add_Shown({$objForm.Activate()})
[void] $objForm.ShowDialog()


Thanks for any help
newbi
2

At a quick glance it looks you are adding arrays, hence the System.Object to the combo box instead of just the text. Change the above line to

$Namen = $worksheet.Cells.Item($Zeile,1).Text
3

Thanks very much for your quick solution!!

4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.