htmlvbaexcelcomboboxweb-scraping

Populate excel user form combo-box from html combo-box with vba


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">
                        &lt;Any&gt;
                    </option>
                    <option value="T488C">
                        T488C
                    </option>
                </select>
            </td>
            <td></td>

Solution

  • 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