Using VBA to retrieve information from google, try

Question

Sub test()
    Dim IE      As New InternetExplorer
    Dim city$, state$
    Dim i       As Integer
    Dim dd      As Variant

    Dim doc     As HTMLDocument  'variable for document or data which need to be extracted out of webpage
    Set doc = IE.document

    i = 2 'start row

    'Setting the variables
    city = Cells(i, 1).Value 'City variable for search
    state = Cells(i, 2).Value 'State variable for search

    Do Until city = ""

        city = Cells(i, 1).Value 'City variable for search
        state = Cells(i, 2).Value 'State variable for search

         'Search google for state and county
        URL = "www.google.com/?safe=active&ssui=on#q=" + city + "+" + state + "+county&safe=active&ssui=on"

        IE.navigate URL
        IE.Visible = False

        Do
            DoEvents
        Loop Until IE.readyState = READYSTATE_COMPLETE

        Application.Wait (Now() + TimeValue("00:00:005")) ' For internal page refresh or loading

        dd = doc.getElementsByClassName("_eF")(0).innerText
        ' Now, trim the name to take the State out
        dd = Left(dd, WorksheetFunction.Search(",", dd) - 1)        

        'set county value
        Cells(i, 3).Value = dd
        'MsgBox dd
        i = i + 1

        'Setting the next variables
        city = Cells(i, 1).Value 'City variable for search
        state = Cells(i, 2).Value 'State variable for search
    Loop

    MsgBox "The macro has finished running"

End Sub

So far this is what I've got thanks to the help of user @BruceWayne

However sometimes google doesn't prompt up the necessary information if the town doesn't have a county or if it is too obscure. I need the macro to record this and keep going. However, every time I try to make an if statement or a fail check the macro stops and prompts the debug window because it is trying to retrieve doc.getElementsByClassName("_eF")(0).innerText.

What can I do to check if doc.getElementsByClassName("_eF")(0).innerText is there and if it is not to record this and keep going?

Thanks!

Answer 1

The methods of retrieving elements from a collection is a zero based index but the .Length property is one based. In other words, the first in the collection of a single element is at position 0 but the length is 1.

if cbool(doc.getElementsByClassName("_eF").LENGTH) then _
    dd = doc.getElementsByClassName("_eF")(0).innerText

Answer 2

I just dealt with this problem recently. I wrote a Function to test if the element I wanted was on the screen. The function is below. I am sure you can modify it for your needs.

Function ScreenTest(oDoc as Object, sLookUp As String) As Boolean
'tests to see if a particular screen is loaded by passing the element name associated with what you would expect to see on the screen
'pass oDoc has your IE document

Dim sTest As String

On Error Resume Next

sTest = oDoc.getElementsByName(sLookUp).item(0).Value

If Err.Number <> 0 Then ScreenTest = False Else: ScreenTest = True

On Error GoTo 0

End Function

To put this in your code, do this:

If ScreenTest(doc, "_eF") Then

    dd = doc.getElementsByClassName("_eF")(0).innerText
    ' Now, trim the name to take the State out
    dd = Left(dd, WorksheetFunction.Search(",", dd) - 1)

    'set county value
    Cells(i, 3).Value = dd
    'MsgBox dd
    i = i + 1

End If

'... continue with rest of code