CODEWITHSUNDEEP
htmlvbainternet-explorer
pmr
pmr

Reputation: 1006

getting error in setting object variable IE.Document.getElement

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 ?

Upvotes: 0

Views: 734

Answers (2)

gembird
gembird

Reputation: 14053

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

Upvotes: 1

Ryan Wildry
Ryan Wildry

Reputation: 5677

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

Upvotes: 1

Related Questions