Parsing a XML Web page file with VBA

Question

I'm working into a MS Access project (2003) and would like to parse a XML code that is returned by a Web page. It is actually on our intranet.

For example, I would like to parse https://myLivelinkServer.myEnterprise.com/livelink/livelink.exe?func=search&boolean2=or&where1=something&where1=anything&outputformat=xml

I would like to get the results into a recordset. Here is an example of some XML generated by the above page with another search query (only 2 results):

<?xml version="1.0" encoding="ISO-8859-1"?>
<Output>
    <SearchResults>
        <SearchResult>
            <OTCreatedBy DisplayName="Créé par">
                <![CDATA[5063560]]>
            </OTCreatedBy>
            <OTLocation DisplayName="Emplacement" Protocol="https" ServerName="livelink.myServer.com" URL="/livelink/livelink.exe?func=ll&amp;objId=53080774&amp;objAction=browse">
                2000 1237258 10236341 12909408 14593490 40170727 53076463 53076481 53080774 60751603 
            </OTLocation>
            <OTMIMEType DisplayName="MIMEType" Protocol="https" ServerName="livelink.myServer.com" IconURL="/llk97s/otemail/emailcontainer.gif">

            </OTMIMEType>
            <OTName DisplayName="Nom" Protocol="https" ServerName="livelink.myServer.com" ViewURL="/livelink/livelink.exe?func=ll&amp;objId=60751603&amp;objAction=browse" DownloadURL="/livelink/livelink.exe?func=ll&amp;objID=60751603&amp;objAction=download">
                2008-07-04 09 22 - ACCUSÉ DE RÉCEPTION - DEMANDE D'ACHAT # 12854509 - Louise Plante
            </OTName>
            <OTObjectDate DisplayName="Date" Mask="YYYY-MM-DD">
                2012-05-29
            </OTObjectDate>
            <OTObjectSize DisplayName="Taille" Suffix="Eléments">
                3
            </OTObjectSize>
            <Functions Protocol="https" ServerName="livelink.myServer.com" ServerPort="443" URL="/livelink/livelink.exe?func=SearchResultsFunctionMenu&amp;hitID=1&amp;searchCacheID=2006218356&amp;version=0&amp;dataID=60751603&amp;HH=false&amp;subtype=264" />
        </SearchResult>
        <SearchResult>
            <OTCreatedBy DisplayName="Créé par">
                <![CDATA[5063560]]>
            </OTCreatedBy>
            <OTLocation DisplayName="Emplacement" Protocol="https" ServerName="livelink.myServer.com" URL="/livelink/livelink.exe?func=ll&amp;objId=60739493&amp;objAction=browse">

            </OTLocation>
            <OTMIMEType DisplayName="MIMEType" Protocol="https" ServerName="livelink.myServer.com" IconURL="/llk97s/webdoc/outlook.gif">
                application/x-outlook-msg
            </OTMIMEType>
            <OTName DisplayName="Nom" Protocol="https" ServerName="livelink.myServer.com" ViewURL="/livelink/livelink.exe?func=ll&amp;objId=60751711&amp;objAction=Open&amp;vernum=1&amp;nexturl=%2Flivelink%2Flivelink%2Eexe%3Ffunc%3Dsearch%26where1%3D12854509%26boolean2%3Dor%26where2%3D3091%2D40014%2D024%2D01%2D0%2DCE%2DL%2DQUF28%2D01%2DUA%26outputformat%3Dxml%26gofor%3D2" DownloadURL="/livelink/livelink.exe?func=ll&amp;objID=60751711&amp;objAction=download">
                2008-07-04 09 22 - ACCUSÉ DE RÉCEPTION - DEMANDE D'ACHAT # 12854509 - Louise Plante
            </OTName>
            <OTObjectDate DisplayName="Date" Mask="YYYY-MM-DD">
                2008-07-04
            </OTObjectDate>
            <OTObjectSize DisplayName="Taille" Suffix="Ko">
                23
            </OTObjectSize>
            <Functions Protocol="https" ServerName="livelink.myServer.com" ServerPort="443" URL="/livelink/livelink.exe?func=SearchResultsFunctionMenu&amp;hitID=2&amp;searchCacheID=2006218356&amp;version=1&amp;dataID=60751711&amp;HH=false&amp;subtype=264" />
        </SearchResult>
    </SearchResults>

    <!-- File: websbroker/resultbar.xml -->
    <SearchResultsInformation>
        <CurrentStartAt>0</CurrentStartAt>
        <NumberResultsThisPage>2</NumberResultsThisPage>
        <RawTotalResults>123</RawTotalResults>
        <EstTotalResults>66</EstTotalResults>
        <TotalResults>3</TotalResults>
        <PreviousQuery>
            <Parameter Name="where1"><![CDATA[12854509]]></Parameter>
            <Parameter Name="boolean2"><![CDATA[or]]></Parameter>
            <Parameter Name="where2"><![CDATA[3091-40014-024-01-0-CE-L-QUF28-01-UA]]></Parameter>
            <Parameter Name="outputformat"><![CDATA[xml]]></Parameter>
            <Parameter Name="gofor"><![CDATA[2]]></Parameter>
        </PreviousQuery>
    </SearchResultsInformation>
    <!-- End File: websbroker/resultbar.xml -->
</Output>

Is there an easy way to open that page with VBA (without any browser obviously) and parse it?

Answer 1

You can load the URL as a MSXML DOMDocument, then use its methods to parse out the values you want.

You can find an abbreviated code outline sample at this SO question: Late Binding conversion for XML import syntax

There are many more VBA code examples here on SO: site:stackoverflow.com vba msxml