Excel VBA - How to get data from XML URL?

Question

I need to get the data from FIELD NAME="sitr2" & FIELD NAME="sitr4". Below is the XML code:

<SEGMENTS>
<SEGMENT NAME="webcluster">
<RESULTPAGE>
<QUERYTRANSFORMS>...</QUERYTRANSFORMS>
<NAVIGATION ENTRIES="0"> </NAVIGATION>
<CLUSTERS/>
<RESULTSET FIRSTHIT="1" LASTHIT="1" HITS="1" TOTALHITS="1" MAXRANK="10000" TIME="0.5673">
<HIT NO="1" RANK="10000" SITEID="0" MOREHITS="0" FCOCOUNT="0">
<FIELD NAME="rank">10000</FIELD>
<FIELD NAME="personnames"/>
<FIELD NAME="concepts">environment;flexible;storage;virtual;growing</FIELD>
<FIELD NAME="companyteaser"/>
<FIELD NAME="locationteaser"/>
<FIELD NAME="personnameteaser"/>
<FIELD NAME="nav">...</FIELD>
<FIELD NAME="taxonomy">t/na</FIELD>
<FIELD NAME="documentid"/>
<FIELD NAME="uniqueid"/>
<FIELD NAME="sitr1">4038b5c5-c90b-4ae3-a165-a5e0db9c10d4</FIELD>
<FIELD NAME="sitr2">097cf4a8-2755-4c62-939c-9402e0a4e3e2</FIELD>
<FIELD NAME="sitr3">Unknown</FIELD>
<FIELD NAME="sitr4">0.3</FIELD>
<FIELD NAME="sitr5"/>
<FIELD NAME="sitr8"><matches> </matches></FIELD>
<FIELD NAME="int1">21854</FIELD>

I got no idea where to start. I need to extract and insert into excel. Hope someone could help me.

score 0 · Answer 1 · answered Jul 23 '18 at 09:24

You can use Internet explorer. Starting page how to use IE in VBA is here: wiseowl

In your case code will be:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub main()
Dim ie As Object
Application.ScreenUpdating = True
Set ie = CreateObject("internetexplorer.application")
With ie
.Visible = True
.navigate "C:\Temp\some.xml"
Do While ie.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
End With
Sleep 300
With ie.document.all.tags("FIELD")
For i = 0 To .Length - 1
Cells(i + 1, 1) = .Item(i).getAttribute("NAME")
Next i
End With
ie.Quit
Set ie = Nothing
End Sub

Excel VBA - How to get data from XML URL?

1 Answers1