Search code examples
htmlexcelvbaweb-scrapinghtml-select

Selecting Multiple Options in Web Scrape

I'm trying to recreate a webscrape that I wrote a few months ago.

There is a selection box to pick different options (multiple allowed) and I can't recall how I wrote selecting certain ones. Below is an outline of what I was doing, and a screenshot of the HTML.

  • Get the inner HTML and paste it into a cell
  • Run some lefts, rights, and finds to extract the skill codes (values) of the options and put them in a vertical list with lookups on each one to return an LOB
  • select an option in the VBA if the skill code matched the LOB I was pulling data for

The most I can remember is after selecting the date, while still in the html document form, I was using something along the lines of

if range[first lookup cell here] = [LOB I was pulling data for] then
    childrow1.selected = true
else
    childrow1.selected = false
End if
if range[second lookup cell here] = [LOB I was pulling data for] then
    childrow2.selected = true
else
    childrow2.selected = false
End if
'Etc, etc

All the selections start selected by default, and I couldn't find a way to deselect all. I know that's not the exact code to use, but that's the gist of what was going on. There are 36 skill codes (so far) and each LOB had 2-8 depending on the LOB. Here's the HTML from the site for the selection box:
Selection Box HTML

<SELECT id=Skill class=summarySmallCallibriCenter multiple size=3 name=Skill rdInputValueDelimiter=",">

<OPTION selected value=44413 Row="1">44413</OPTION>

<OPTION selected value=44414 Row="2">44414</OPTION>

<OPTION selected value=44415 Row="3">44415</OPTION>

<OPTION selected value=44416 Row="4">44416</OPTION>

<OPTION selected value=44420 Row="5">44420</OPTION>

<OPTION selected value=44421 Row="6">44421</OPTION>

<OPTION selected value=44422 Row="7">44422</OPTION>

<OPTION selected value=44423 Row="8">44423</OPTION>

<OPTION selected value=44426 Row="9">44426</OPTION>

<OPTION selected value=44433 Row="10">44433</OPTION>

<OPTION selected value=44485 Row="11">44485</OPTION>

<OPTION selected value=44486 Row="12">44486</OPTION>

<OPTION selected value=44491 Row="13">44491</OPTION>

<OPTION selected value=44492 Row="14">44492</OPTION>

<OPTION selected value=44494 Row="15">44494</OPTION>

<OPTION selected value=44495 Row="16">44495</OPTION>

<OPTION selected value=44496 Row="17">44496</OPTION>

<OPTION selected value=44497 Row="18">44497</OPTION>

<OPTION selected value=44500 Row="19">44500</OPTION>

<OPTION selected value=44505 Row="20">44505</OPTION>

<OPTION selected value=44506 Row="21">44506</OPTION>

<OPTION selected value=44507 Row="22">44507</OPTION>

<OPTION selected value=44508 Row="23">44508</OPTION>

<OPTION selected value=44509 Row="24">44509</OPTION>

<OPTION selected value=44510 Row="25">44510</OPTION>

<OPTION selected value=44516 Row="26">44516</OPTION>

<OPTION selected value=44523 Row="27">44523</OPTION>

<OPTION selected value=44535 Row="28">44535</OPTION>

<OPTION selected value=44536 Row="29">44536</OPTION>

<OPTION selected value=44537 Row="30">44537</OPTION>

<OPTION selected value=44539 Row="31">44539</OPTION>

<OPTION selected value=44544 Row="32">44544</OPTION>

<OPTION selected value=44545 Row="33">44545</OPTION>

<OPTION selected value=44548 Row="34">44548</OPTION>

<OPTION selected value=44555 Row="35">44555</OPTION>

<OPTION selected value=44556 Row="36">44556</OPTION>

</SELECT>

I'm really only looking for an answer on what code to put there, but if there's also an easier way to do this process I'm all ears. The reason I grab the inner html from the selection box is because, while not often, the skill codes do change, so I wanted to prepare for future changes. As it was before I lost it, there were 6 macros (one for each LOB) each with the above code 36 times. It may not have been the most efficient, but it got the job done.

Code until this point:

Sub Service_Level_Pull()

Dim ieApp As Object
Dim ieDoc As Object
Dim ieTable As Object
Dim clip As DataObject
Dim NextToDo As String
Dim ntdrng As Range
Dim NextEmptyDone As Long 

Set ieApp = GetObject("new:{D5E8041D-920F-45e9-B8FB-B1DEB82C6E5E}")

ieApp.Visible = True
 
ieApp.Navigate [login link here]
While ieApp.Busy
    DoEvents
Wend
        
Set ieDoc = ieApp.Document
 
Do While ieApp.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Loop
        
With ieDoc.forms(0)
    .UserName.Value = [username here]
    .UserPass.Value = [pw here]
End With
With ieDoc.forms(0)
    .submit
End With
Do While ieApp.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Loop
Do  
    ieApp.Navigate [webscrape target link here]
    Do While ieApp.ReadyState <> READYSTATE_COMPLETE
        DoEvents
    Loop

    With ieDoc.forms(0)
        .ipStartDate.Value = (Date - Day(today)) + 1
        .ipEndDate.Value = today - 1
        With ieDoc.forms(0).skill
Solution

  • I've figured it out! I changed the way a few of the things were calculated, and the bit of code I ended up writing is:

    Do Until n = SkillCount


    With ieDoc.forms(0)
    .ipStartDate.Value = Format(Date - Day(Date) + 1, "mm/dd/yyyy")
    .ipEndDate.Value = Format(Now() - 1, "mm/dd/yyyy")
    End With
  Set drp = ieDoc.forms(0).skill



    If Application.WorksheetFunction.CountIf(Range(Cells(3, y).Address, Cells(Cells(1, y).Value + 2, y).Address), drp.Children(n).Value) > 0 Then
    drp.Children(n).Selected = True
    Else
    drp.Children(n).Selected = False
    End If

    n = n + 1
    Loop

    I was able to find a way to print the options in the drop-down in a column that then had some lookups to reference the codes with LOB's and then I listed all the LOB's to the right of it with formulas to grab the first/next selection from the drop-down. I made more adjustments to what I put in the original post, and some variables in this snippet that I omitted the definitions to, but this snippet was the reason for the post so I decided to just grab that. If anyone who stumbles across this thread sees something similar in their own code and wants me to put the whole thing here to see where y and n are defined and such just comment and I'll edit it in.

    Hopefully this helps anyone else who is dealing with webscrapes that include drop-down selections