Excel Macro Extracting Data from Website

[joman]

I am currently experiencing a problem pulling some data of a web site using Excel. I have a collection of HTMLInputElement objects from which I extract the one I want. I then set the value (text) of the object and submit the form. Here is my code:
...
Dim inputElements As IHTMLElementCollection
Dim inputCounter As Integer
Dim inputETFIndex As Integer
Do
Dim etfInput As HTMLInputElement
ie.Visible = False

ie.Navigate "http://finance.yahoo.com/etf"
Do
Loop While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE

Set doc = ie.document
Set inputElements = doc.getElementsByTagName("INPUT")

If Counter = 1 Then
For inputCounter = 0 To inputElements.Length - 1
Set inputele = inputElements.Item(inputCounter)
If inputele.Name = "s" And inputele.Size = 7 Then
Set etfInput = inputele
inputETFIndex = inputCounter
Exit For
End If
Next inputCounter
Else
Set etfInput = inputElements.Item(inputETFIndex)
End If

etfInput.Value = ETFSymbol
etfInput.form.submit
...

The problem is the line etfInput.Value = ETFSymbol
where I get the error message: "Object Variable or With Block Variable Not Set".

Any assistance would be greatly appreciated.

 

[QuietRiot]

is it because of the 2 DO's?

you never end the loop

 

[joman]

I did complete the loop in my code.

 

[Norie]

Could it be because what you are looking for is never found?

If it isn't then etfSymbol will be Nothing.

Also have you tried finding what you are looking for using another method?

Something like this perhaps.

    Set IE = CreateObject("InternetExplorer.application")
    IE.Visible = True
    IE.Navigate ("http://finance.yahoo.com/etf")
    Do
        If IE.ReadyState = 4 Then
            Exit Do
        Else
            DoEvents
        End If
    Loop
    
    Set doc = IE.Document
    
    Set ctl = doc.getElementById("txtQuotes")
    
    ctl.Value = "ETFSymbol"
    
    doc.Forms(1).submit ' ctl.Form.submit
    IE.Quit

 

[joman]

Thanks for your response. When I run the following code, the execution stops on the second line complaining about an object variable or with block variable not set. I have an outer loop that iterates over the different ETFSymbols.

Set doc = ie.document
doc.getElementById("txtQuotes").Value = ETFSymbol
doc.forms(1).submit

When I debug the code, the program execution stops on the second line but I can press F8 and the run time engine does not complain. I tried on error resume next but the that appears to give wierd results.

 

[Norie]

Well all I can say is the code I posted works for me, and that is all the code.

It's messy, I've not declared any variables, not referenced anything, it should have a loop after submitting the form etc.

And obviously I'm using a bogus symbol, 'EFTSymbol' - so no 'real' results.

How exactly did you try the code?

PS Don't add On Error Resume next, if you are having a problem step through the code and try and figure out what it is.

On Error... can have it's uses but can also hide errors.

 

[joman]

Hi,
I managed to get it to work by adding a sleep statement before the:

set doc = ie.document
[\code]
 
I'm not sure if this is the best approach.
 
Perhaps
[code]
Do
Loop While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE
[\code]
is not working properly

 

[John_w]

Try

    IE.navigate "http://finance.yahoo.com/etf"       
    While IE.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    While IE.document.readyState <> "complete": DoEvents: Wend        
    Set doc = IE.document

 

[joman]

I appears to be working with the above looping statements which wait for internet explorer to finish loading. Thanks!