Web Scrapping: select item form List Box (DropDown List) by Name

Guzman · Dec 4, 2017

Hi,

I'm trying to write a VBA script for Web Scrapping. I have to select an item form a ListBox (<select> tag in HTML) by its name.

Does anybody know how to do this?

I'm using this libraries:
1. Microsoft Internet Controls
2. HTML Object Library

Here you have some of the HTML code:

Code:

<select onchange="redirect(this.value);" name="select" id="select" style="width: 165px;">                
<option value="">Select a fund</option>                            
  <option value="/wps/myportal/mfs/non-us-advisor/products/meridian-funds/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTwEojHfOSjC3S9aOKUtNSi1KL9DLyi0v0I8rLy_Vy04r1kvNz9cOd9aOSKjx88gL1C7KjkiodFRUBZk22dA!!/dl5/d5/L2dJQSEvUUt3QS80SmlFL1o2X09RMlFPN0gyMEczU0MwMkQ0TzM1VTMxRk8y/?clearPortletSession=true&productId=480&shareId=33&viewMode=profile">                                                                  
      <!-- Do Nothing -->
      Absolute Return Fund                            
  </option>                      
        <option value="/wps/myportal/mfs/non-us-advisor/products/meridian-funds/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTwEojHfOSjC3S9aOKUtNSi1KL9DLyi0v0I8rLy_Vy04r1kvNz9cOd9aOSKjx88gL1C7KjkiodFRUBZk22dA!!/dl5/d5/L2dJQSEvUUt3QS80SmlFL1o2X09RMlFPN0gyMEczU0MwMkQ0TzM1VTMxRk8y/?clearPortletSession=true&productId=1466&shareId=33&viewMode=profile">
      <!-- Do Nothing -->
          Asia Pacific Ex-Japan Fund                            
  </option>                      
  <option value="/wps/myportal/mfs/non-us-advisor/products/meridian-funds/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTwEojHfOSjC3S9aOKUtNSi1KL9DLyi0v0I8rLy_Vy04r1kvNz9cOd9aOSKjx88gL1C7KjkiodFRUBZk22dA!!/dl5/d5/L2dJQSEvUUt3QS80SmlFL1o2X09RMlFPN0gyMEczU0MwMkQ0TzM1VTMxRk8y/?clearPortletSession=true&productId=139597&shareId=33&viewMode=profile">
      <!-- Do Nothing -->    
      Blended Research European Equity Fund                
  </option>
</select>

I would like to select the item by Name, like: Absolute Return Fund, Asia Pacific Ex-Japan Fund, Blended Research European Equity Fund.

I have Option Explicit feature on, so I would appreciate if you specify the variable type as well.

I'm not an expert programmer, so anything will help.
(I've cross posted, I'll be poting all the answers from the different forums)

Chihiro · Dec 4, 2017

Option is picked by Value. And not by displayed text.

So, you need to set option value to corresponding one.

At any rate, without your base code, we can't really suggest a variable. There are plenty of examples within this forum for this as well.

Guzman · Dec 4, 2017

Chihiro said:
Option is picked by Value. And not by displayed text.

So, you need to set option value to corresponding one.

At any rate, without your base code, we can't really suggest a variable. There are plenty of examples within this forum for this as well.

How would you do it by Value?
I've searched in the forum and Google, but I didn't exactly find what I was looking for. Any example?

THANKS!

Chihiro · Dec 4, 2017

First post your code to access the site. From there I can help you. Without it, I have no idea if there's anything else other than option value that I need to worry about.

Simple example:

Code:

Set dropOpt = .getElementsByTagname("select")
dropOpt.Value = "/wps/myportal/mfs/non-us-advisor/products/meridian-funds/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTwEojHfOSjC3S9aOKUtNSi1KL9DLyi0v0I8rLy_Vy04r1kvNz9cOd9aOSKjx88gL1C7KjkiodFRUBZk22dA!!/dl5/d5/L2dJQSEvUUt3QS80SmlFL1o2X09RMlFPN0gyMEczU0MwMkQ0TzM1VTMxRk8y/?clearPortletSession=true&productId=1466&shareId=33&viewMode=profile"

However, I don't know if there are any other "select" tag element within the site. Nor do I know what references you've added to your code etc etc. You need to provide more info in order for us to help you.

Chihiro · Dec 4, 2017

So I assume you have MS Internet Controls and MS HTML Object Library added as reference in your project. Any other?

FYI - I'd recommend editing your code to remove the password and login details.

Guzman · Dec 4, 2017

Chihiro said:
First post your code to access the site. From there I can help you. Without it, I have no idea if there's anything else other than option value that I need to worry about.

However, I don't know if there are any other "select" tag element within the site. Nor do I know what references you've added to your code etc etc. You need to provide more info in order for us to help you.

Code:

Option Explicit
Sub Login_ToMFS()

' *** LOGIN TO MFS WEBSITE ***

Dim IE As New SHDocVw.InternetExplorer

Dim URL As String
URL = "https://www.mfs.com/wps/myportal/mfs/non-us-advisor/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTgErDnYB6zXODk9OBuiId85KMLdL1o4pS01KLUov0MvKLS_QjysvL9XLTivWS83P1C7Kj3NIdFRUBBU7kpw!!/dl5/d5/L2dBISEvZ0FBIS9nQSEh/#"


IE.Visible = True
IE.Navigate URL

Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
Loop

Dim HTMLDoc As MSHTML.HTMLDocument
Set HTMLDoc = IE.Document

' *** Check credentials and autologin

' GET USER ID (HTML code)
Dim UserIDCode As String
UserIDCode = "userId"
Dim HTMLInputUser As MSHTML.IHTMLElement
Set HTMLInputUser = HTMLDoc.getElementById(UserIDCode) 'User ID

If Not HTMLInputUser Is Nothing Then ' Check if user input exists

    ' USER ID
    Dim UserIDValue As String
    UserIDValue = "****"
    HTMLInputUser.Value = UserIDValue
  
    ' GET PASSWORD (HTML code)
    Dim UserPasswordCode As String
    UserPasswordCode = "paswordId"
    Dim HTMLInputPassword As MSHTML.IHTMLElement
    Set HTMLInputPassword = HTMLDoc.getElementById(UserPasswordCode) 'Password
  
    ' PASSWORD
    Dim UserPasswordValue As String
    UserPasswordValue = "****"
    HTMLInputPassword.Value = UserPasswordValue
  
    ' Login and submit
    Dim HTMLButtonLogin As MSHTML.IHTMLElement
    Dim HTMLButtonsLogin As MSHTML.IHTMLElementCollection
  
    Dim LoginButton As String
    LoginButton = "login_btn"
    Set HTMLButtonsLogin = HTMLDoc.getElementsByClassName(LoginButton)
  
    For Each HTMLButtonLogin In HTMLButtonsLogin
        Dim LoginValue As String
        LoginValue = "Login"
        If HTMLButtonLogin.getAttribute("value") = LoginValue Then
            HTMLButtonLogin.Click
            Exit For
        End If
    Next HTMLButtonLogin
End If

Application.Wait DateAdd("s", 5, Now)

Call MFS_Global_HighYield(IE) 'To look up for the specific fund
End Sub
Sub MFS_Global_HighYield(IE As SHDocVw.InternetExplorer)
' Select from a ListBox by Name

' **** CODE HERE ****

' SEE: "SELECT A FUND"

End Sub

Guzman · Dec 4, 2017

Chihiro said:
So I assume you have MS Internet Controls and MS HTML Object Library added as reference in your project. Any other?

FYI - I'd recommend editing your code to remove the password and login details.

Hi,
I've created a new user in the website, so you can access with no problem, later I'll erase it anyway. But you can have access to the HTML code easily. - I'm not John Willims

Yes, I'm using this libraries:
1. Microsoft Internet Controls
2. HTML Object Library

Chihiro · Dec 4, 2017

Something like below to select using .innerText of option element.

Code:

Option Explicit
Sub Login_ToMFS()

' *** LOGIN TO MFS WEBSITE ***

Dim IE As New SHDocVw.InternetExplorer

Dim URL As String
URL = "https://www.mfs.com/wps/myportal/mfs/non-us-advisor/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTgErDnYB6zXODk9OBuiId85KMLdL1o4pS01KLUov0MvKLS_QjysvL9XLTivWS83P1C7Kj3NIdFRUBBU7kpw!!/dl5/d5/L2dBISEvZ0FBIS9nQSEh/#"


IE.Visible = True
IE.Navigate URL

Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
Loop

Dim HTMLDoc As MSHTML.HTMLDocument
Set HTMLDoc = IE.Document

' *** Check credentials and autologin

' GET USER ID (HTML code)
Dim UserIDCode As String
UserIDCode = "userId"
Dim HTMLInputUser As MSHTML.IHTMLElement
Set HTMLInputUser = HTMLDoc.getElementById(UserIDCode) 'User ID

If Not HTMLInputUser Is Nothing Then ' Check if user input exists

    ' USER ID
  Dim UserIDValue As String
    UserIDValue = "JohnWilliams"
    HTMLInputUser.Value = UserIDValue

    ' GET PASSWORD (HTML code)
  Dim UserPasswordCode As String
    UserPasswordCode = "paswordId"
    Dim HTMLInputPassword As MSHTML.IHTMLElement
    Set HTMLInputPassword = HTMLDoc.getElementById(UserPasswordCode) 'Password

    ' PASSWORD
  Dim UserPasswordValue As String
    UserPasswordValue = "7101099123asd"
    HTMLInputPassword.Value = UserPasswordValue

    ' Login and submit
  Dim HTMLButtonLogin As MSHTML.IHTMLElement
    Dim HTMLButtonsLogin As MSHTML.IHTMLElementCollection

    Dim LoginButton As String
    LoginButton = "login_btn"
    Set HTMLButtonsLogin = HTMLDoc.getElementsByClassName(LoginButton)

    For Each HTMLButtonLogin In HTMLButtonsLogin
        Dim LoginValue As String
        LoginValue = "Login"
        If HTMLButtonLogin.getAttribute("value") = LoginValue Then
            HTMLButtonLogin.Click
            Exit For
        End If
    Next HTMLButtonLogin
End If

Application.Wait DateAdd("s", 5, Now)

Call MFS_Global_HighYield(IE, "Asia Pacific Ex-Japan Fund") 'To look up for the specific fund
End Sub
Sub MFS_Global_HighYield(IE As SHDocVw.InternetExplorer, sval As String)
Dim optCol As Object
Dim i As Long

Set optCol = IE.Document.getElementById("select")
    For i = 0 To optCol.Length - 1
        If InStr(optCol(i).innerText, sval) > 0 Then
            optCol(i).Selected = True
            Exit For
        End If
    Next

End Sub

Guzman · Dec 4, 2017

Something interesting is happening.

The code does select the option, that works. But if you see, it selects it but the page doesn't load.

That is because you don't have to press "ok" or "continue", when you manually select the option it automatically loads the next page, but somehow when doing it with VBA it doesn't happen.

Do you understand what I mean?

THANKS.

Chihiro · Dec 4, 2017

Likely because you need to fire the java script. Don't have time now to test it. But you should somehow fire onchange="redirect(this.value)".

I'll see if I have time later. But hopefully others will chime in here

Guzman · Dec 4, 2017

Thanks a lot for the help. If by any chance you have some spare time, give it a try

I'll leave the questions as unsolved for the moment, so anybody can come and make suggestions.

THANKS A LOT.

shahin · Dec 5, 2017

Hi there, I could not log in using the credentials you have provided. So i could go thus far. I've tried to concise your code to give a slightly pleasant look. If it is possible, provide me with some information so that i can get in and encounter the barrier you are facing.

Code:

Sub Login_ToMFS()
    Const URL As String = "https://www.mfs.com/wps/myportal/mfs/non-us-advisor/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTgErDnYB6zXODk9OBuiId85KMLdL1o4pS01KLUov0MvKLS_QjysvL9XLTivWS83P1C7Kj3NIdFRUBBU7kpw!!/dl5/d5/L2dBISEvZ0FBIS9nQSEh/#"
    Dim IE As New SHDocVw.InternetExplorer, HTMLInputUser As Object, HTMLDoc As HTMLDocument
    Dim HTMLButtonLogin As Object

    With IE
        .Visible = True
        .navigate URL
        Do While .readyState <> READYSTATE_COMPLETE: Loop
        Set HTMLDoc = .document
    End With
  
    Set HTMLInputUser = HTMLDoc.getElementById("userId")
    If Not HTMLInputUser Is Nothing Then
        HTMLDoc.getElementById("userId").Value = "JohnWilliams"
        HTMLDoc.getElementById("paswordId").Value = "7101099123asd"
      
        For Each HTMLButtonLogin In HTMLDoc.getElementsByClassName("login_btn")
            If HTMLButtonLogin.getAttribute("value") = "Login" Then HTMLButtonLogin.Click: Exit For
        Next HTMLButtonLogin
    End If
    Application.Wait Now + TimeValue("00:00:03")
End Sub

Chihiro · Dec 5, 2017

Untested. But first try converting Sub MFS_Global_HighYield to Function.

Code:

Function MFS_Global_HighYield(IE As SHDocVw.InternetExplorer, sval As String) As String
Dim optCol As Object
Dim i As Long, mS As String

Set optCol = IE.Document.getElementById("select")
    For i = 0 To optCol.Length - 1
        If InStr(optCol(i).innerText, sval) > 0 Then
            optCol(i).Selected = True
            mS = optCol(i).Value
            Exit For
        End If
    Next
MFS_Global_HighYield = mS
End Function

Then modify call line and below in the main sub to...

Code:

Dim jVal As String
jVal = MFS_Global_HighYield(IE, "Asia Pacific Ex-Japan Fund")
Call IE.document.parentWindow.execScript("redirect(" & jVal & ")", "JavaScript")
End Sub

Guzman · Dec 5, 2017

Hi Everybody!!!
Thank you very much for all your suggestions.

I think that I've reached a solution that I'll share here.
Please notice that I've changed the credentials, updated in code now.

Code:

Option Explicit

Sub Login_ToMFS()

' *** LOGIN TO MFS WEBSITE ***

Dim IE As New SHDocVw.InternetExplorer

Dim URL As String
URL = "https://www.mfs.com/wps/myportal/mfs/non-us-advisor/!ut/p/a1/04_Sj9CPykssy0xPLMnMz0vMAfGjzOL9A40C_c09jAzcjYOdDYxcTPyNTUONDS1NjPW99KPSc_KTgErDnYB6zXODk9OBuiId85KMLdL1o4pS01KLUov0MvKLS_QjysvL9XLTivWS83P1C7Kj3NIdFRUBBU7kpw!!/dl5/d5/L2dBISEvZ0FBIS9nQSEh/#"


IE.Visible = True
IE.Navigate URL

Do While IE.Busy Or IE.ReadyState <> READYSTATE_COMPLETE
Loop

Dim HTMLDoc As MSHTML.HTMLDocument
Set HTMLDoc = IE.Document

' *** Check credentials and autologin

' GET USER ID (HTML code)
Dim UserIDCode As String
UserIDCode = "userId"
Dim HTMLInputUser As MSHTML.IHTMLElement
Set HTMLInputUser = HTMLDoc.getElementById(UserIDCode) 'User ID

If Not HTMLInputUser Is Nothing Then ' Check if user input exists

    ' USER ID
    Dim UserIDValue As String
    UserIDValue = "JohnWilliams"
    HTMLInputUser.Value = UserIDValue

    ' GET PASSWORD (HTML code)
    Dim UserPasswordCode As String
    UserPasswordCode = "paswordId"
    Dim HTMLInputPassword As MSHTML.IHTMLElement
    Set HTMLInputPassword = HTMLDoc.getElementById(UserPasswordCode) 'Password

    ' PASSWORD
    Dim UserPasswordValue As String
    UserPasswordValue = "John123qwe"
    HTMLInputPassword.Value = UserPasswordValue

    ' Login and submit
    Dim HTMLButtonLogin As MSHTML.IHTMLElement
    Dim HTMLButtonsLogin As MSHTML.IHTMLElementCollection
  
    Dim LoginButton As String
    LoginButton = "login_btn"
    Set HTMLButtonsLogin = HTMLDoc.getElementsByClassName(LoginButton)

    For Each HTMLButtonLogin In HTMLButtonsLogin
        Dim LoginValue As String
        LoginValue = "Login"
        If HTMLButtonLogin.getAttribute("value") = LoginValue Then
            HTMLButtonLogin.Click
            Exit For
        End If
    Next HTMLButtonLogin
End If

Application.Wait DateAdd("s", 20, Now)

Call MFS_Global_HighYield(IE, "Absolute Return Fund") 'To look up for the specific fund
End Sub
Sub MFS_Global_HighYield(IE As SHDocVw.InternetExplorer, sval As String)

Dim optCol As MSHTML.HTMLSelectElement
Dim i As Long

Set optCol = IE.Document.getElementById("select")
    For i = 0 To optCol.Length - 1
        If InStr(optCol(i).innerText, sval) > 0 Then
            optCol(i).Selected = True
            Exit For
        End If
    Next

'Fire JS event
optCol.FireEvent ("onchange")
End Sub

The questions has been solved.
Thanks.

Web Scrapping: select item form List Box (DropDown List) by Name

Guzman

New Member

Chihiro

Excel Ninja

Guzman

New Member

Chihiro

Excel Ninja

Chihiro

Excel Ninja

Guzman

New Member

Guzman

New Member

Chihiro

Excel Ninja

Guzman

New Member

Chihiro

Excel Ninja

Guzman

New Member

shahin

Active Member

Chihiro

Excel Ninja

Guzman

New Member