VBA Parsing XML contained in HTMLpage from IE Document

Question

I have a VBA program that uses IE Automation to query a web site a classical way: Const URL = "xxxx" Dim ieApp As InternetExplorer Dim oHTMLDoc As HTMLDocument

ieApp.Navigate URL
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set oHTMLDoc = ieApp.Document
.....etc

For some reasons, I have to use the InternetExplorer object - I can't use MSXML2 library to query the site. Later in the program, the result of the query is pure XML - which is a pain to parse using HTML routines.

So my simple question: how can I transfer a HTMLDocument into an MSXML2.DOMDocument ?

Dim oXMLDoc As MSXML2.DOMDocument
Set oXMLDoc = oHTMLDoc  'Fails

Thank you.

If the query returns pure XML then you can use MSXML for that part... — Tim Williams
– Tim Williams, Commented Aug 20, 2015 at 4:59
Thanks Tim - I understand that but this is my question:how can I do that ? I need to use IE and oHTMLDoc to authenticate, how can I switch to MSXLM for further queries ? An Open/Send with XMLHTTP returns "not authenticated". — user1238760
– user1238760, Commented Aug 20, 2015 at 13:12
I'm unclear on the actual workflow here. In normal operation via IE, what's the browser doing with something which is sent as pure XML? — Tim Williams
– Tim Williams, Commented Aug 20, 2015 at 14:44
I am accessing HP Agile Manager server. The recommendation to login is to use REST_API with XMLHTTP type of request - once you authenticate you receive pure XML to every query.Unfortunately I have not been successful to authenticate using the res_api (I know, that's the right way to do it...) so I am logging in using IE but then I am stuck when the reply is pure XML. Of course I can go through the HTML DOM...but that is not really practical. So my question. Hope this clarifies ! — user1238760
– user1238760, Commented Aug 20, 2015 at 14:52
I think it might be less work to figure out how to authenticate against the REST API using MSXML than to continue using IE... — Tim Williams
– Tim Williams, Commented Aug 20, 2015 at 15:52

user1238760 · Accepted Answer · 2015-08-20 23:26:14Z

1

Thanks to jdweng I have the answer:

Dim ieApp As InternetExplorer
Dim oHTMLDoc As HTMLDocument
Dim sXML as String 

ieApp.Navigate *URL*
Do While ieApp.Busy: DoEvents: Loop
Do Until ieApp.ReadyState = READYSTATE_COMPLETE: DoEvents: Loop

Set oHTMLDoc = ieApp.Document
Set oXML = New MSXML2.DOMDocument60

sXML = oHTMLDoc.DocumentElement.outerHTML
If Not oXML.LoadXML(sXML) Then _
    Err.Raise oXML.parseError.ErrorCode, , oXML.parseError.reason

And I can enjoy XML in my oXML document ! Thanks again.

answered Aug 20, 2015 at 23:26

user1238760

612 silver badges6 bronze badges

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

Comments

jdweng · Accepted Answer · 2015-08-20 09:33:58Z

0

Try this

'add reference Microsoft XML, v6.0
Public Sub DownloadFile()
    Dim objWHTTP As Object
    Dim strPath As String
    Dim arrData() As Byte
    Dim lngFreeFile As Long



    On Error Resume Next
        Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5")
        If Err.Number <> 0 Then
            Set objWHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")
        End If
    On Error GoTo 0

    URL = "Enter URL Here"

    objWHTTP.Open "GET", URL, False
    objWHTTP.send
    arrData = objWHTTP.responseBody
    strData = StrConv(arrData, vbUnicode)

    Dim fedbook As New MSXML2.DOMDocument60

    fedbook.LoadXML strData

answered Aug 20, 2015 at 9:33

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

5 Comments

user1238760 Over a year ago

Thank you jdweng. This does not work for me unfortunately. I must start with IE and oHTML document - I can't switch to the WinHTTP Get because I loose my authentification token. The question is how to get from a HTMLDocument to an XML document.

jdweng Over a year ago

You need to look at source of webpage to determine what tags the XML is wrapped with. Normally I go to webpage with an IE manually and then using menu select View : Source. Or download the webpage to a file on computer and view file with text editor. You can even look at the OutterXML in the download document.

user1238760 Over a year ago

Ok - that what it looks like:

user1238760 Over a year ago

<?xml version="1.0" encoding="UTF-8" standalone="true"?> -<Entities TotalResults="1557"> -<Entity Type="release-backlog-item"> -<Fields> <Field Name="no-of-sons"/> <Field Name="release-id"/> -<Field Name="entity-type"> <Value>requirement</Value> </Field>

jdweng Over a year ago

Once you get the XML string like you have posted then you can use the LoadXML method like in my example. I used strData as my string. If you are getting bytes, then convert using StrConv like in my example.

Collectives™ on Stack Overflow

VBA Parsing XML contained in HTMLpage from IE Document

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related