getting error in setting object variable IE.Document.getElement

Question

I was trying to get some data from a webpage by giving a keyword "AABDG27" and then click on search button and finally collecting the search results last item that is market data category= ESZ. It is an open website so below code can be tested. Below is my code.It gives error in the line Set mydata = IE.Document.getElements....

Sub keyword_search_result()

    Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
    Dim mydata As Object
    Dim myval As String

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Visible = False
    IE.Navigate "http://www.platts.com/symbol-page-directories/symbol-search"

    Do
        DoEvents
    Loop Until IE.ReadyState = 3

    Do
        DoEvents
    Loop Until IE.ReadyState = 4

    IE.Document.getElementById("ctl00_ctl00_contentBody_contentMain_txtSearchSymbol").Value = "AABDG27"
    IE.Document.getElementById("ctl00_ctl00_contentBody_contentMain_btnSearch").Click

    Do
        DoEvents
    Loop Until IE.ReadyState = 4


    Set mydata = IE.Document.getElementsByClassName("divCellBottomMiddle")(0).getElementsByTagName("br")

     i = 0
     For Each objCollection In mydata
     myval = mydata(i).PreviousSibling.wholeText
     'Debug.Print i&; " "; myval
     i = i + 1
     If i = 11 Then
     'this is the case when myval string will come as "ESZ" 
     'but with lot of space
     Sheets("Sheet1").Range("A1") = Trim(Right(myval, 10))
     End If
     Next objCollection

     IE.Quit
     Set IE = Nothing


End Sub

so what should be changed, is there any better way to do it utilizing winhttprequest/XMLHTTP or something like that ?

gembird · Accepted Answer · 2016-08-30 16:07:31Z

1

One more idea is to check, if the HTML-Element-Collection is not Nothing and if it contains some elements. Otherwise error will occur because of access to elements which are not in the collection when nothing is found with 'search'.

Dim cellBottomMiddle ' HTML-Element-Collection 
Set cellBottomMiddle = IE.Document.getElementsByClassName("divCellBottomMiddle")

If cellBottomMiddle Is Nothing Then GoTo finalize
If cellBottomMiddle.Length <= 0 Then GoTo finalize

Set mydata = cellBottomMiddle(0).getElementsByTagName("br")

' Some code here ...

finalize:
    IE.Quit
    Set IE = Nothing

answered Aug 30, 2016 at 16:07

gembird

14.1k5 gold badges39 silver badges54 bronze badges

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

Comments

Ryan Wildry · Accepted Answer · 2016-08-30 15:56:24Z

1

I reworked your code to make this a bit easier to understand as well as make finding the element in the table a bit easier.

I changed how the element is being found, by adding it to a String Array. From here you can simply select the last item in the array, instead of iterating over it to find the last item. I also added the wait section for the IE Object into a separate Sub to reduce the amount of code.

Here's the code, I have it working on my end.

Sub keyword_search_result()
    Dim IE As Object: Set IE = CreateObject("InternetExplorer.Application")
    Dim StrArray() As String

    With IE
        .Visible = False
        .Navigate "http://www.platts.com/symbol-page-directories/symbol-search"
        'Wait for the page to load
        IELoad IE
        .Document.getElementById("ctl00_ctl00_contentBody_contentMain_txtSearchSymbol").Value = "AABDG27"
        .Document.getElementById("ctl00_ctl00_contentBody_contentMain_btnSearch").Click
        'Wait again
        IELoad IE
    End With

    'Put the string into an array, split by line breaks
    StrArray = Split(IE.Document.getElementsByClassName("divCellBottomMiddle")(0).InnerText, vbCrLf)

    'Retrieve the last element in the array, place in Excel Range A1
    Sheets("Sheet1").Range("A1") = StrArray(UBound(StrArray))

    'Clean Up
    IE.Quit
    Set IE = Nothing
End Sub

Public Sub IELoad(Browser As Object)

    While Browser.ReadyState <> 4 Or Browser.Busy
        Application.Wait (Now() + TimeValue("00:00:01"))
    Wend

End Sub

answered Aug 30, 2016 at 15:56

Ryan Wildry

5,6971 gold badge17 silver badges36 bronze badges

3 Comments

pmr Over a year ago

@Ryan..very unfortunately it is giving same object variable error in line StrArray = Split(IE.Document.. ideally it should not have happened but it is happening at my end

Ryan Wildry Over a year ago

You ran the code exactly as above? E.g. no changes made? What version of IE are you running? I believe IE 8+ support getElementsByClassName .Works without issue on my end. If this error still appears, try adding another IeLoad IE prior to line that's causing the issue.

Ryan Wildry Over a year ago

Update: I think IE 9+ version work with getElementsByClassName. See: w3schools.com/jsref/met_element_getelementsbyclassname.asp

Collectives™ on Stack Overflow

getting error in setting object variable IE.Document.getElement

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related