Search code examples
vbaweb-scrapingdropdown

How to select li-tag with vba?

i have some trouble with a vba code. I want to get some data from an internal website at my company into excel-sheets. I already achieved to change dates and run some for-next thru different websites and at the end I want to extract the data from the sites to some spreadsheets.

But this is breaking me: I want to select a specific value (here "B") in a dropdown-field (li) and update the site.

Here is a part of the intranet-site:

<div class="dropdown btn-group">
<button class="btn btn-default dropdown-toggle" type="button" data-toggle="dropdown" aria-expanded="false">
<span class="dropdown-text">B</span>
<span class="caret"></span>
</button><ul class="dropdown-menu" role="menu">
<li class="" aria-selected="false">
<a data-action="A" class="dropdown-item dropdown-item-button">A</a>
</li>
<li aria-selected="true" class="active">
<a data-action="B" class="dropdown-item dropdown-item-button">B</a>
</li>
<li aria-selected="false" class="">
<a data-action="C" class="dropdown-item dropdown-item-button">C</a>
</li>
<li aria-selected="false" class="">
<a data-action="-1" class="dropdown-item dropdown-item-button">All</a>
</li>
</ul>
</div>

And here is a short vba-script, without the other loops and data-stuff. With that i can read out the values o the dropdown-list - but how can I select one (i.e. "B") and update the page?

Sub GetData()
 Dim IE As New InternetExplorer, html As HTMLDocument
 Dim elem As Object, data As String

 With IE
  .Visible = True
  .navigate "site from intranet"
  Do While .readyState <> READYSTATE_COMPLETE: Loop
  Set html = .document
 End With
 data = ""
 For Each elem In html.getElementsByClassName("dropdown-menu")(0).getElementsByTagName("li")
 data = data & " " & elem.innerText
 Next elem
 Range("A1").Value = data
 IE.Quit
End Sub

Since it is from our intranet I cannot post the whole code, hopefully you can give me some advice anyway.

Solution

  • You can try using an attribute = value css selector

    ie.document.querySelector("[data-action=B]").click

    I use a click as I see an anchor element.

    You may need to setAttribute "class", "active" on the parent li ; and/or setAttribute "aria-selected", True