I've made the basis of reading the data from a website XML, like this:
Sub Sheet2_Button1_Click()
Dim ie As InternetExplorer 'to refer to the running copy of Internet Explorer
Dim html As HTMLDocument 'to refer to the HTML document returned
Set ie = New InternetExplorer 'open Internet Explorer in memory, and go to website
ie.Visible = False
ie.navigate "http://ift.tt/1wmBGeE" ' this is not a true address of the website that I'm accessing
Do While ie.READYSTATE <> READYSTATE_COMPLETE 'Wait until IE is done loading page
Application.StatusBar = "Trying to go to the page"
DoEvents
Loop
Set html = ie.document 'show text of HTML document returned
Set ie = Nothing 'close down IE and reset status bar
Application.StatusBar = ""
Cells.Clear 'clear old data out and put titles in
Range("C2").Value = "Player 1"
Range("D2").Value = "Player 2"
Range("E2").Value = "Time"
' and other cells
' insert the code for reading the XML file
' insert the code for filling the Excel cells with read data from XML file
Set html = Nothing
Application.StatusBar = ""
MsgBox "Done!"
End Sub
The XML file that Excel has to read is like this:
<commentary version="3" refreshRate="10" language="en" server_time="2014-12-07 18:41:20"><event id="6923570" stats_avail="Y" score_avail="Y" perform_avail="N" unas_avail="N" stream_avail="N">Name</event><eventheader locale="en"><translation token="defaultMessage">Commentary will appear shortly</translation><bestOf>3</bestOf></eventheader><competitors><competitor id="0">AAA</competitor><competitor id="1" active="Y">BBB</competitor></competitors><clock state="S" period="ns">00:00</clock><summary period="total"><statsum id="ace" competitor="0">0</statsum><statsum id="ace" competitor="1">0</statsum><statsum id="brk" competitor="0">2</statsum><statsum id="brk" competitor="1">2</statsum><statsum id="dbl" competitor="0">4</statsum><statsum id="dbl" competitor="1">3</statsum><statsum id="gme" competitor="0">4</statsum><statsum id="gme" competitor="1">3</statsum><statsum id="scr" competitor="0">15</statsum><statsum id="scr" competitor="1">0</statsum><statsum id="set" competitor="0">0</statsum><statsum id="set" competitor="1">0</statsum><statsum id="st1" competitor="0">4</statsum><statsum id="st1" competitor="1">3</statsum></summary><summary period="st1"><statsum id="ace" competitor="0">0</statsum><statsum id="ace" competitor="1">0</statsum><statsum id="brk" competitor="0">2</statsum><statsum id="brk" competitor="1">2</statsum><statsum id="dbl" competitor="0">4</statsum><statsum id="dbl" competitor="1">3</statsum><statsum id="gme" competitor="0">4</statsum><statsum id="gme" competitor="1">3</statsum><statsum id="scr" competitor="0">15</statsum><statsum id="scr" competitor="1">0</statsum><statsum id="set" competitor="0">0</statsum><statsum id="set" competitor="1">0</statsum></summary><messages><msg id="50462952" type="dbl" period="st1" competitor="1" time="1121:13">CCC</msg></messages></commentary>
This complete XML is in one line without line breaks. I need to catch server_time, competitor id="0", competitor id="1", active="Y", and all statsum ids. As much as I have found at the net, I shoud do it by using DOM, but I don't know how.
Can someone help?
Thanks in advance, regards, Dean
No comments:
Post a Comment