Use substring in Excel VBA with (xml)data

Question

I'd like to do some simple substringing in Excel VBA, alas string.substring(a,b) does not seem to work.

I have before me a very simple xml format such as this one:

<result>
<rowset name="typeids" key="typeID" columns="typeName,TypeID">
<row typeName="Tritanium" typeID="34" />
<row typeName="Scordite" typeID="1228" />

more rows

</rowset>
</result>
</eveapi>

I'd ideally like to get an array or concatenated string with all typeIDs in the order they appear here.

Now, I could of course just InStr myself to death, but I'd like to know whether one of you can come up with a more elegant solution since my VBA knowledge is quite limited.

I'd also be grateful for answers that just parse the xml instead of viewing it as a simple string - I'd generally just like to keep it on the simple side since this is my first contact with VBA ever.

Thanks a lot.

I'm not picky in that regard, it could either be a string such as "34,1228,(etc)" or an array of integers of the typeIDs. — Dennis Röttger
– Dennis Röttger, Commented Dec 1, 2013 at 15:06

Gary's Student · Accepted Answer · 2013-12-01 15:30:57Z

1

This assumes that the data occupies column A:

Sub FindTypeID()
    Dim sOut As String, st As String, N As Long, NN As Long
    Dim DQ As String
    DQ = Chr(34)
    s1 = "typeID=" & DQ
    s2 = DQ & " />"
    NN = Cells(Rows.Count, "A").End(xlUp).Row
    For N = 1 To NN
        st = Replace(Cells(N, 1).Text, s2, "")
        ary = Split(st, s1)
        If UBound(ary) = 1 Then
            sOut = sOut & "," & ary(1)
        End If
    Next N
    MsgBox sOut
End Sub

answered Dec 1, 2013 at 15:30

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

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

3 Comments

Dennis Röttger Over a year ago

The Data exists as a String as a result of a webservice I've called - should've added that to the question. How would I populate a Column with this data before calling your function (this is my very first contact with Excel VBA altogether)?

Gary's Student Over a year ago

I am not sure..........can you get the webservice to store the data as a text file on your computer??................If yes, then we can import the file into a worksheet.

Dennis Röttger Over a year ago

I just solved the problem myself without writing the data into a sheet first - I'll make sure to use a solution similar to yours in the future though, seems a little more elegant.

Dennis Röttger · Accepted Answer · 2013-12-01 15:59:54Z

0

I've used the most direct approach to solve this problem now.

In case anyone might be having the same problem in the future, here's the code I used -

If Len(xmlData) > 0 Then
      k = xmlData

    Do While InStr(k, "typeID=") <> 0
        j = InStr(k, "typeID=")
        k = Right(k, Len(k) - j)
        numberStart = InStr(k, """")
        numberEnd = InStr(numberStart + 1, k, """")
        numbers = numbers & Mid(k, numberStart + 1, numberEnd - numberStart - 1) & ","
    Loop
    numbers = Left(numbers, Len(numbers) - 1)
End If

The result is a String containing all the TypeIDs in a comma-separated fashion.

answered Dec 1, 2013 at 15:59

Dennis Röttger

1,9857 gold badges32 silver badges52 bronze badges

Collectives™ on Stack Overflow

Use substring in Excel VBA with (xml)data

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related