How to take value from excel workbook to find something in a web form dropbox

Question

I am working on a web automation project. I have come up to a web drop box to where I have to choose a value. I have a query selector which works fine in doing the job but now I want that query selector to find those values in the drop box which are taken from a cell value of excel sheet.

    With .document.querySelector("[value='536']")
        .Selected = True
        .dispatchEvent evt
    End With

Above is the query which works fine. But now I want to replace "536" from cell "A1" . I have tried to replace ("[value='536']") with ([value='thisWorkbool.sheets.("sheet1").Range(A1).value']") but it shows error.

Option Explicit

Public Sub FillForm()
    Dim ie As Object, tool As Workbook

    Set tool = ActiveWorkbook
    Set ie = CreateObject("InternetExplorer.application")

    With ie
        .Visible = True
        .navigate "https://******.com/"

        While .Busy Or .readyState < 4: DoEvents: Wend

        With .document
            .getElementById("txtUserName").Value = "09100107801-01"
            .getElementById("txtPassword").Value = "Abc@1234"
            .getElementById("btnSubmit").Click
        End With

        While .Busy Or .readyState < 4: DoEvents: Wend

        .navigate "https://***************"

        While .Busy Or .readyState < 4: DoEvents: Wend

        Dim evt As Object
        Set evt = .document.createEvent("HTMLEvents")
        evt.initEvent "change", True, False

        With .document.querySelector("[value='536']")
            .Selected = True
            .dispatchEvent evt
        End With

end sub

How to put the value from the cell value "A1" of exel sheet in above code.

Tim Williams · Answer 1 · 2019-05-04T04:12:10.847

0

Something like this should work:

Dim v
v = thisWorkbook.sheets("sheet1").Range("A1").value
With .document.querySelector("[value='" & v & "']")
    .Selected = True
    .dispatchEvent evt
End With

edited May 04 '19 at 04:12

answered May 04 '19 at 03:34

Tim Williams

154,628
8
97
125

Thanx for your so prompt response but when I substitute the above value in shows compile error. variable not defined. – Dev May 04 '19 at 03:55
Thanx Tim for helping me so promptly. It worked and now everything is working fine. – Dev May 04 '19 at 07:34

How to take value from excel workbook to find something in a web form dropbox

1 Answers1