How do I place focus and click submit button using VBA in Excel?

Question

I am new to web scraping and VBA but am eager to learn further. I have a small project in mind and am taking it slowly one step at a time. I have a small piece of code in an Excel module which is almost working. I just cannot get it to submit my search box text. I basically have the example horse name in Cell A2 in my Excel worksheet. The code opens the website and also enters the text into the search box. I just can't get it to click the Go button. I'd appreciate any help and an explanation of what I'm doing wrong so I can avoid it again!

When inspecting the Go button on the site, the HTML is thus:

My code:

Sub HorseSearch()

    'define objIE
    Dim objIE As InternetExplorer
    'create an instance objIE
    Set objIE = New InternetExplorer
    'make web page visible
    objIE.Visible = True

    'navigate objIE to this web page
    objIE.navigate "https://www.britishhorseracing.com/racing/horses/racehorse-search-results/"

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'in the search box put cell "A2" value which holds a horse name Tiger Roll
    objIE.document.getElementById("text-search").Value = Sheets("Sheet1").Range("A2").Value

    'place focus on the Go button
    objIE.document.getElementById("Submit").Focus

    'click the 'go' button
    objIE.document.getElementById("Submit").Click

End Sub

Igor · Accepted Answer · 2020-05-01 15:00:42Z

1

Improving your code

Your problem is that when you fire the click on the submit button it is not enabled. You have to enable it. If you observe it the submit button got enabled when you manually insert a text into the input box, with a length > 3 characters. Your code insert the text changing the value attribute of the input box but it doesn't fire the 'onchange' event. Using web developer tools in Chrome I saw that the form behaviour (input box vs submit button) is built in jQuery.

One of the possible solutions can be to use objIE.Document.parentWindow.ExecScript "jQuery(""#text-search"").trigger(""change"")" after inserting the value and before clicking on the submit button.

Here the complete code:

Sub HorseSearch()

    'Dim objIE As Object
    'Set objIE = CreateObject("InternetExplorer.Application")

    Dim obJe As InternetExplorer
    Set objIE = New InternetExplorer
    'make web page visible
    objIE.Visible = True

    'navigate objIE to this web page
    objIE.navigate "https://www.britishhorseracing.com/racing/horses/racehorse-search-results/"

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'in the search box put cell "A2" value which holds a horse name Tiger Roll

    objIE.Document.getElementById("text-search").Value = Sheets("Sheet1").Range("A2").Value
    On Error Resume Next
    objIE.Document.parentWindow.ExecScript "jQuery(""#text-search"").trigger(""change"")"
    On Error GoTo 0

    'click the 'go' button
    objIE.Document.getElementById("Submit").Click


End Sub

Another solution

Why to use the form when we can directly go to the result page? Result page is something like https://www.britishhorseracing.com/racing/horses/racehorse-search-results/#!?pagenum=1&q=tiger%20roll&rated=false

where the q parameter value is the text you put in the input box. We could navigate directly to that page (using the value from cell A22 for q parameter in the URL).

Sub HorseSearch()

    'Dim objIE As Object
    'Set objIE = CreateObject("InternetExplorer.Application")

    Dim obJe As InternetExplorer
    Set objIE = New InternetExplorer
    'make web page visible
    objIE.Visible = True

    'navigate objIE to this web page
    objIE.navigate "https://www.britishhorseracing.com/racing/horses/racehorse-search-results?pagenum=1&q=" & Sheets("Sheet1").Range("A2").Value & "&rated=false"

    'wait here a few seconds while the browser is busy
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop

    'You have loaded the result page :)

End Sub

answered May 1, 2020 at 15:00

Igor

1577 bronze badges

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

3 Comments

Gareth Lloyd Over a year ago

Igor.......you are an absolute legend! Thank you so much for taking the time to share your knowledge with me. This has definitely spurred me on to continue now!

Gareth Lloyd Over a year ago

Wowzers!.....this is tough to get my head around! My next issue albeit probably an easy one is a line of code to click and activate that page with the horse's name on. Either I just click the horse name which is a link or extract the horse's ref number and navigate directly to the horse's stats page. I can't seem to do either.......

Igor Over a year ago

I don't know very well community rules but I think it's better to post a new question (leaving here in comments a link).

Collectives™ on Stack Overflow

How do I place focus and click submit button using VBA in Excel?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related