help with html extraction in vba

[neeeel]

there is a bit of code I have written, which works on my pc, but doesnt work on someone elses. I am really confused.the code in question is

[COLOR=blue]Dim[/COLOR] temp [COLOR=blue]As[/COLOR] HtmlHtmlElement  [COLOR=blue] 
Dim[/COLOR] s [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]   
s = "2222222"   
[COLOR=blue]For Each[/COLOR] temp [COLOR=blue]In[/COLOR] html.getElementsByTagName("option")      [COLOR=blue]   
    If[/COLOR] temp.getAttribute("value") = s [COLOR=blue]Then[/COLOR]             
    r.Offset(0, 1) = (temp.innerText)               [COLOR=blue]   
    End[/COLOR] [COLOR=blue]If[/COLOR]  [COLOR=blue] 
Next[/COLOR] temp

r is a range object that is passed to the sub.
the variable html is an object that has been loaded with html from a webpage.

This code works fine on my pc, it finds the "option" tags in the html source , and then checks to see if the "value" attribute is equal to the string s. When I run it on someone elses pc , temp.getAttribute("value") returns a blank string, even though there is an attribute called value. The web page address is hard coded so its not that hes using the wrong URL
I use excel 2007, he uses 2010

Anyone got any ideas?
thanks

 

[neeeel]

Are you loading the XMLHttp ResponseText into a HTMLDocument? If so, how?

Yes, the html variable in the OP is an HTMLDocument, i am loading it using

html.body.innerHTML = xml.responseText

thanks
neil

 

[John_w]

I think the webpage has finished loading, how would I check if it hasnt?

Or more importantly, this will wait until it has finished loading:

     Set html = New HTMLDocument
    html.body.innerHTML = XML.responseText
    Do While html.readyState <> "complete"
        DoEvents
        Application.Wait Now + TimeValue("00:00:01")
    Loop

You might need to include a timeout in the loop to prevent it waiting forever if readyState is never "complete".

If this doesn't work you may have to do what I first suggested and check for the existence of a specific HTML element.

 

[neeeel]

If this doesn't work you may have to do what I first suggested and check for the existence of a specific HTML element.

How do you mean exactly?

 

[John_w]

Firstly, did the readyState loop solve the problem?

Try something like this to wait for the existence of a specific HTML element (also shows the loop timeout I mentioned):

Sub Wait_for_HTML_element()

    Dim html As HTMLDocument
    Dim elem As HTMLGenericElement
    Dim timeout As Date, t As Single
    
    timeout = Now + TimeValue("00:00:30")
    Set elem = Nothing
    While Now < timeout And elem Is Nothing
        Set elem = Get_Element(html)
        If elem Is Nothing Then t = Timer: While Timer - t < 0.25: Wend     'wait 0.25 seconds
        DoEvents
    Wend
                    
    If elem Is Nothing Then
        Debug.Print "Element not found"
    Else
        Debug.Print "Element found"
    End If

End Sub


Private Function Get_Element(HTMLdoc As HTMLDocument) As HTMLGenericElement
    
    Dim elems As IHTMLElementCollection
    Dim thisElem As HTMLGenericElement
    Dim i As Integer
    
    Set elems = HTMLdoc.getElementsByTagName("xxx")
    Set Get_Element = Nothing
    i = 0
    While i < elems.Length And Get_Element Is Nothing
        Set thisElem = elems(i)
        If thisElem.nodeName = "yyy" And thisElem.nodeValue = "zzz" Then Set Get_Element = thisElem
        i = i + 1
    Wend
    
End Function

The Sub shows the general algorithm. The Get_Element function would be coded for your specific web page, and may not necessarily use the techniques shown. For instance, you might use getElementsById; or a specific HTML element like HTMLtableCell rather than HTMLGenericElement; or you might loop through the HTMLDocument.all collection; or look at a specific element property rather than nodeName, etc., etc. It all depends on your specific web page and your specific requirement.

 

[neeeel]

thanks for your help.
Im not clear on when I would use this, after I have done the .send command to get the page, or after I have checked the ready state? or where?

At least I have a couple of things to try out, although I am sceptical that its due to a not fully loaded page

thanks

 

[Norie]

Just passing.

To check an XMLHTTP request has completed you need to do something like this.

        Set oXMLHTTP = New XMLHTTP
 
        oXMLHTTP.Open "GET", strURL, False
 
        oXMLHTTP.send
 
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop

You can also check the status using either the status or statusText property, for example:

If oXMLHTTP.status = 200 Then

 

[ZVI]

oXMLHTTP.Open "GET", strURL, False

The same point of view. Much seems that 3d parameter of asynchronous request in GET was not set to False