Excel VBA - How to get data from XML URL? [duplicate]

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.

The xml is also not closed.

QHarr
– QHarr

2018-07-29 07:55:42 +00:00
Commented Jul 29, 2018 at 7:55 — QHarr
– QHarr, Commented Jul 29, 2018 at 7:55

Tomáš Marný · Accepted Answer · 2018-07-23 09:24:32Z

0

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

answered Jul 23, 2018 at 9:24

Tomáš Marný

611 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA - How to get data from XML URL? [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related