Search code examples
vbaexcelie-automation

VBA IE automation through excel - Get drop down list attribute values


I am trying to get drop down values written into excel, however i am having some difficulties.

Heres is the HTML code:

<div class="select__box" id="wrapBox">
   <div class="option" name="US" data-id="United States">
      <div class="option__icon"><span class="flag-icon flag-icon-3x flag-icon-us flag-icon-squared option__flag"></span></div>
      <div class="option__title">United States</div>
   </div>
   <div class="option" name="CA" data-id="Canada">
      <div class="option__icon"><span class="flag-icon flag-icon-3x flag-icon-ca flag-icon-squared option__flag"></span></div>
      <div class="option__title">Canada</div>
   </div>
   <div class="option" name="GB" data-id="United Kingdom">
      <div class="option__icon"><span class="flag-icon flag-icon-3x flag-icon-gb flag-icon-squared option__flag"></span></div>
      <div class="option__title">United Kingdom</div>
   </div>
   <div class="option" name="DO" data-id="Dominican Republic">
      <div class="option__icon"><span class="flag-icon flag-icon-3x flag-icon-do flag-icon-squared option__flag"></span></div>
      <div class="option__title">Dominican Republic</div>
   </div>
   <div class="option" name="CN" data-id="China">
      <div class="option__icon"><span class="flag-icon flag-icon-3x flag-icon-cn flag-icon-squared option__flag"></span></div>
      <div class="option__title">China</div>
   </div>
</div>

Firstly I need to get the country names, I am able to do this with code

x = objIE.document.getElementsByClassName("option").Length
    nextEmptyRow = 1

    For i = 0 To x - 1
    cnt = objIE.document.getElementsByClassName("option")(i).innerText
    ActiveWorkbook.Sheets("Sheet1").Range("A" & nextEmptyRow).Value = cnt
    nextEmptyRow = nextEmptyRow + 1
    Next i

Then I want to get the country codes (US, CA, GB, DO, CN)

I am trying to use similar code, but it gives me an error, and I am not able to find out what could be the issue.

    For i = 0 To x - 1
    cnt2 = objIE.document.getElementByClassName("option")(i).getAttribute("name").innerText
    ActiveWorkbook.Sheets("Sheet1").Range("B" & nextEmptyRow2).Value = cnt2
    nextEmptyRow2 = nextEmptyRow2 + 1
    Next i

Solution

  • Thank you for the suggestion, QHarr, but I have found a simpler solution.

    Apparently I needed to indicate the lines in the HTML code more specifically, I was missing getElementById("wrapBox") and .innerText or .Value was unnecessary.

     For i = 0 To x - 1
        cnt = objIE.document.getElementsByClassName("option")(i).innerText
        ActiveWorkbook.Sheets("Sheet1").Range("A" & nextEmptyRow).Value = cnt
        cnt2 = objIE.document.getElementById("wrapBox").getElementsByClassName("option")(i).getAttribute("name")
        ActiveWorkbook.Sheets("Sheet1").Range("B" & nextEmptyRow).Value = cnt2
        nextEmptyRow = nextEmptyRow + 1
        Next i