CODEWITHSUNDEEP
vbaexcel
yu_ominae
yu_ominae

Reputation: 2935

Retrieving website xml with Excel

In Excel2003 I am trying to retrieve some content from Yahoo finance.

I have a really simple sub so far:

Public Sub test(BaseRange As Range)

    Dim xmlObject As New MSXML2.XMLHTTP60
    Dim xmlDoc As New MSXML2.DOMDocument

    With xmlObject
        Call .Open("GET", "http://biz.yahoo.com/research/earncal/20110923.html", False)
        Call .send
    End With


    With ActiveSheet.Range("F2")
        .Value = xmlObject.responseXML.XML
    End With

End Sub

The sub executes without errors and the xmlObject.responseText seems to contain the page html text, but xmlObject.responseXml.XML is empty. Apart from resonsetext everything is actually empty.

What am I missing here?

Upvotes: 0

Views: 7775

Answers (3)

Stephan Semerad
Stephan Semerad

Reputation: 91

This is what I used for the ECB FX rate. 

Sub ecb_fx()
ActiveWorkbook.XMLIMPORT url:= _

        "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml", ImportMap:=Nothing, _
        Overwrite:=True, Destination:=Range("$B$6")
End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166366

There's only content in responseXml if the server returns an XML response. Seems like your URL returns HTML and not XML.

Upvotes: 1

JustinJDavies
JustinJDavies

Reputation: 2693

It is worth pointing out that there is an open source project that does this already: http://excel-dna.net/2011/04/28/financial-analytics-suite-finansu-made-with-excel-dna/

I hope that this is useful to you

Upvotes: 2

Related Questions