Need to extract data from XML format and convert into new Excel row

Veev

New Member
Joined
Jun 10, 2011
Messages
11
First, let me say thanks to everyone who contributes their time here. You guys have been incredibly helpful and prompt in your replies. I have one final problem I need sorted out and then I can leave you all in peace. :wink: This may also be too big of a task to just ask for some quick/simple help with. I wouldn't be opposed to compensating you for your time (if the forum rules allow for that).

I am working with the following XML file: http://www.veevsvault.com/veev/guild.xml

Under the Members group, I need to extract a list of every Name that has IsOnline set to True. I need these names pasted in a horizontal row in another worksheet, with one name per cell.

I am slightly familiar with recording and using macros in Excel, so if there is an automatic way I could filter out this data, I could potentially take that route. And I am able to copy/paste the XML file into its own sheet in Excel, but if there was a way to directly extract the data from a pre-set file path that would be even better.

And that's all that I really need done. If it's not too much trouble I'd also like to replace the Name tag with its associated OfficerNotes tag, ONLY if Rank = 1, 3, or 9.

Again, I realize I'm probably asking for a lot here, so I'd understand if it's too much time to volunteer. Here is the spreadsheet I am working with, if it helps to see exactly what I'm attempting to do: http://www.veevsvault.com/veev/rift.xlsm -- I want to filter out the needed data on the Import tab, and then paste it on a new line in the Attendance tab.

Thanks for taking the time to read.
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi there,

Consider:

Code:
Sub GetDataFromXML()

    'read from the XML file and output to sheet

    Application.Calculation = xlCalculationManual

    'retrieve the full name (path, name and extension) of the file

    sFullFilename = "C:\Program Files (x86)\RIFT Game\guild.xml"

    'check if the file could be found

    If Len(Dir(sFullFilename)) = 0 Then

        'the file could not be found

        MsgBox "The specified file" & vbNewLine & sFullFilename & vbNewLine & "could not be found. Please check.", vbCritical, "Invalid option"

        'stop the code execution

        Exit Sub

    End If

    'read in the file contents and split

    Open sFullFilename For Input As #1

    v = Split(Split(Input(LOF(1) - 1, #1), "<Members>")(1), vbCrLf)

    Close #1

    'filter on certain line contents

    vName = Filter(v, "<Name>")

    vRank = Filter(v, "<Rank>")

    vIsOnline = Filter(v, "<IsOnline>")

    vOfficerNotes = Filter(v, "<OfficerNotes>")

    With ThisWorkbook.Worksheets("Attendance")
        
        sOutput = Date & "##"

        'loop through the returned names

        For I = 0 To UBound(vName)

            'check the person was online (field 'IsOnline' = True)

            If StripTags(vIsOnline(I)) = "True" Then

                'create the output in a string, containing the necessary information

                'it could be the name, or the OfficerNotes if the rank obeys certain rules
                
                sOutput = sOutput & IIf(InStr("#1#3#9#", "#" & StripTags(vRank(I)) & "#"), StripTags(vOfficerNotes(I)), StripTags(vName(I))) & "#"

            End If
        Next
        
        vt = Split(sOutput, "#")
        .Cells(.Range("A" & .Rows.Count).End(xlUp).Row + 1, 1).Resize(, UBound(vt)).Value = vt

    End With
    
    Application.Calculation = xlCalculationAutomatic

End Sub

Function StripTags(sText As Variant)

'custom function to deal with XML lines, stripping out leading and trailing spaces, and tags

    sNewText = Trim$(sText)

    StripTags = Mid(sNewText, InStr(sNewText, ">") + 1, Len(sNewText) - 2 * InStr(sNewText, ">") - 1)

End Function

Wigi
 
Upvote 0
For kicks I tried an xslt on your file. It seems to work but I'm not very experienced with xml - so take with a grain of salt:

Code:
Private Sub Foo()

Dim doc As Object
Dim xsl As Object
Dim str As String
Dim a
    
    Set doc = CreateDOM
    doc.Load ThisWorkbook.Path + "\guild.xml"
    
    Set xsl = CreateDOM
    xsl.Load ThisWorkbook.Path + "\guild.xsl"
    
    str = doc.transformNode(xsl)
    If Right(str, 1) = "," Then
        str = Left(str, Len(str) - 1)
    End If
    
    If InStr(1, str, "?>") > 1 Then
      str = Right(str, Len(str) - InStr(1, str, "?>") - 1)
    End If
    
    MsgBox str '//comma delimited string
    a = Split(str, ",") '//array of values (can be written to an Excel range)
    
End Sub

Private Function CreateDOM() As Object
Dim dom
    
    Set dom = CreateObject("MSXML2.DOMDocument.5.0")
    dom.async = False
    dom.validateOnParse = False
    dom.resolveExternals = False
    Set CreateDOM = dom

End Function

-----------------------------------------

I've assumed a document called guild.xml in the same folder as the workbook this code runs in. Also a document called guild.xsl in the same folder as well, with the following contents:

Code:
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <xsl:template match="/">
        <xsl:for-each select="Guild/Members/Member">
            <xsl:choose>
                <xsl:when test="IsOnline='True'">
                  <xsl:choose>
                      <xsl:when test="Rank = 9 or Rank = 1 or Rank = 3">
                          <xsl:value-of select="OfficerNotes"></xsl:value-of>
                          <xsl:if test="position()!=last()">,</xsl:if>
                      </xsl:when>
                      <xsl:otherwise>
                          <xsl:value-of select="Name"></xsl:value-of>
                          <xsl:if test="position()!=last()">,</xsl:if>
                      </xsl:otherwise>
                  </xsl:choose>
                </xsl:when>
            </xsl:choose>
      </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,741
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top