VBA button click working sometimes

Question

I'm just getting started with VBA and I was following a simple tutorial to use VBA to make a Google Search in IE.

Sub FillInternetForm()
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "https://www.google.com"
IE.Visible = True
While IE.busy
DoEvents 'wait for page to finish loading
Wend
IE.Document.All("q").Value = ThisWorkbook.Sheets("sheet1").Range("a1")
IE.Document.All("btnK").Click
End Sub

I ran this code multiple times (by pressing f5) and it appears that IE.Document.All("btnK").Click only works sometimes. Sometimes the Macros pop up will appear, sometimes not. I was just wondering what could be the cause of this or if there's anyway to prevent this from happening.

I am using Excel 2013

Thank you

What do you mean by sometimes pops up? You see that box when running the code? — QHarr
– QHarr, Commented Jun 18, 2018 at 21:54
The pop up only comes up sometimes when I press f5. When it pops up I have to click "run" to begin the marco. When it doesn't pop up, the macro will simply run. — Jummi
– Jummi, Commented Jun 18, 2018 at 22:18

QHarr · Accepted Answer · 2018-06-19 05:20:11Z

1

You could avoid the search button altogether and concatenate the search term into the URL direct. For example:

Option Explicit
Sub FillInternetForm()
    Dim IE As Object, searchTerm As String
    searchTerm = ThisWorkbook.Worksheets("Sheet1").Range("A1")
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .navigate "https://www.google.co.uk/search?safe=strict&source=hp&ei=XiooW8rAKMeZsgGji57gBw&q=" & searchTerm & "&oq=" & searchTerm & "&gs_l=psy-ab.3..0l2j0i131k1j0l2j0i131k1j0j0i131k1l3.1481.2118.0.2518.7.6.0.0.0.0.124.555.0j5.6.0..3..0...1.1.64.psy-ab..1.6.657.6..35i39k1.103.n22OrWdTQrs"
        .Visible = True
        While .Busy Or .readyState < 4: DoEvents: Wend
     Stop '<= Delete this line
    .Quit
     End With
End Sub

If you do an example search you can capture the URL and the edit in your variable name to string, as I show above.

As @RyszardJędraszyk explains (many thanks), you may have a dynamically loaded webpage in which content is not available at the moment you try to grab hold of an element. In this case you can have a loop which attempts to set to an element that you know should be present and loop while is nothing (with timeout).

Ryszard further points out you are missing your ReadyState = 4 fully loaded (which does mean a dynamic page, for example, is actually fully loaded, or that a lazy-loading page has all items visible (you may require scrolling to achieve this. My answer above shows you how to also use the ReadyState.

I was unsure about the cause of this pop-up, so I just went with the simplest route to get the page of interest, to try and lessen the risk of this happening. It is not something I have ever had happen or could reproduce I'm afraid.

edited Jun 19, 2018 at 5:20

answered Jun 18, 2018 at 21:59

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

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

6 Comments

Jummi Over a year ago

Thanks for the quick response. I understand what you're doing but I would like to know the underlining cause of why "Click" only works sometimes, or I just happen to be unlucky.

Ryszard Jędraszyk Over a year ago

@Jummi You don't check ReadyState property of IE object in DoEvents loop. 4 means fully loaded.

Jummi Over a year ago

@RyszardJędraszyk So I modified my code to have While IE.busy Or IE.Readystate < 4 but this doesn't appear to correct the issue.

Ryszard Jędraszyk Over a year ago

@Jummi Sometimes JavaScript loads on the page as well. Neither Busy nor ReadyState keep a track of it. Try adding 5 second wait before IE.Document.All("q").Value line, Application.Wait (Now + TimeValue("00:00:05")) If with this line code always executes successfully, then it is JS fault.

QHarr Over a year ago

Jummi - Ryszard has given you an excellent couple of explanation points. I guess I should have mentioned these in my hurry to bypass them. I will update.

|

Collectives™ on Stack Overflow

VBA button click working sometimes

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related