I have a user form that I would like the html option values to populate the excel combo box. Basically I want to duplicate the values and pass them later.
What I have is smashed together from various posts but nothing seems to work.
Dim appIE As InternetExplorerMedium
Dim nam As Object
Dim sel As Object
Set appIE = New InternetExplorerMedium
sURL = "site infor goes here"
With appIE
.navigate sURL
.Visible = True
End With
Do While appIE.Busy Or appIE.readyState <> 4
DoEvents
Loop
For Each f In IE.document.getElementsByTagName("select")
If f = "suppliercode" Then
For Each fOption In IE.document.getElementsByTagName("option")
With Me.SupplierSite.AddItem(f.Option)
End With
Next fOption
End If
Next f
ALSO TRIED:
Set Doc = IE.document.forms("NewReleaseQueueForm1")
For Each sel In Doc.getElementsByTagName("select")(0).Value
If sel.Name = "suppliercode" Then
'loop through and add each option to Me.SupplierSite
For Each opt In IE.document.forms("NewReleaseQueueForm1").getElementsByTagName("option")(0).Value
Me.SupplierSite.AddItem sel.Value
Next opt
End If
Next sel
HTML Sample:
<form id="NewReleaseQueueForm1" method="post" name="NewReleaseQueueForm1">
<table cellpadding="4">
<tr>
<th valign="top">Supplier Site</th>
<td valign="top">
<select multiple name="suppliercode" size="5">
<option selected value="Any">
<Any>
</option>
<option value="T488C">
T488C
</option>
</select>
</td>
<td></td>
Set IE = IE.document.frames(1).document
Dim supls As Object
Dim suplsDrop As Object
Set suplsDrop = IE.getElementsByTagName("OPTION")
For Each supls In IE.getElementsByTagName("SELECT")
If supls.Name = "suppliercode" Then
For Each suplsDrop In supls
With Me.SupplierSite
.AddItem suplsDrop.Value
End With
Next suplsDrop
End If
Next supls