VBA code to select a value from webform dropdown based on value in excel workbook

Question

I'm needing to select a value from a webform dropdown based on the value in the excel book where I'm writing the macro. So far I've been able to navigate to the website and click the desired tab with the following vba code:

Sub FillInternetForm()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")

    ie.Navigate "website I'm navigating to"
    ie.Visible = True
    While ie.Busy
        DoEvents 'wait until IE is done loading page.
    Wend

    Set AllHyperLinks = ie.Document.getElementsByTagName("A")
    For Each Hyper_link In AllHyperLinks
        If Hyper_link.innerText = "Reconciliations" Then
            Hyper_link.Click
            Exit For
        End If
    Next
End Sub

Next, I'm needing to click either "Preparer", "Approver", or "Reviewer" based on a predefined value (cell reference) within the workbook where I'm attempting to write the macro. Below is the html coding that I believe I need to reference within my macro to perform the action described:

<td class="DashControlTableCellRight"><select name="ctl00$MainContent$ucDashboardPreparer$ucDashboardSettings$ctl00$ddlRoles" class="ControlDropDown" id="ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlRoles" onchange="OnRoleChanged(this);">
<option selected="selected" value="Preparer">Preparer</option>
<option value="Reviewer">Reviewer</option>
<option value="Approver">Approver</option>

</select></td>

K.Dᴀᴠɪs · Accepted Answer · 2018-01-31 06:42:26Z

6

I first want to point out that using ie.busy by itself is dangerous. .busy is very unreliable when you are automating web pages, and I would recommend that you also include the .readyState property in your loop.

See this test I ran using a loop using .readyState < 4:

Notice how .Busy was true for the first 5 lines, then became false on line 6? This is where your code would have thought the webpage was loaded. However, .readyState was still 1 (which is the equivalent to READYSTATE_LOADING)

All of a sudden it became busy again until .readystate = 4 (READYSTATE_COMPLETE).

I have moved your .busy method into a separate sub because this is something that is called quite often when navigating web pages.

Sub ieBusy(ie As Object)
    Do While ie.busy Or ie.readystate < 4
        DoEvents
    Loop
End Sub

Sub FillInternetForm()
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")

    ie.navigate "website I'm navigating to"
    ie.Visible = True

    iebusy ie

    Set AllHyperLinks = ie.document.getElementsByTagName("A")
    For Each Hyper_link In AllHyperLinks
        If Hyper_link.innerText = "Reconciliations" Then
            Hyper_link.Click
            Exit For
        End If
    Next

    iebusy ie

    Dim mySelection As String, mySelObj As Object
    Set mySelObj = ie.document.getElementById("ctl00_MainContent_ucDashboardPreparer_ucDashboardSettings_ctl00_ddlRoles")

    'set your selection here
    mySelection = "Preparer"    'or Reviewer    or Approver
    Select Case mySelection
    Case "Preparer", "Reviewer", "Approver"
    Case Else
        MsgBox "Invalid Selection!"
        ie.Quit
        Exit Sub
    End Select

    mySelObj.Value = mySelection

End Sub

The mySelObj is the object that will set your selection.

edited Jan 31, 2018 at 6:42

answered Jan 28, 2018 at 18:42

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Grant Bangerter Over a year ago

K.Davis - Thanks you very much for the detail you provided in response to my question. When I debug I'm getting the following error on the following code line

Grant Bangerter Over a year ago

Error: The remote server machine does not exist or is invalid

Grant Bangerter Over a year ago

Code Line: Set mySelObj = ie.Document.getElementsByName("ctl00$MainContent$ucDashboardPreparer$ucDashboardSettings$ctl00$ddlRoles")(0)

K.Dᴀᴠɪs Over a year ago

@GrantBangerter Can you recheck if that name in your HTML you provided changed? If so we will need to look at a different route. Also, usually that error means that you closed out of ie - you may want to set ie.visible = False so you don't accidentally close it.

Grant Bangerter Over a year ago

@K.Davis - Your latest updated resolved my issue. Thanks you very much for your help and timely responses.

|

Collectives™ on Stack Overflow

VBA code to select a value from webform dropdown based on value in excel workbook

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related