Getting N-th string between two substrings might sound a bad idea, until you do not need it. And with VBA, you better have something prepared.
The N-th value in XML.
So, imagine the following XML (from this article here):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<FootballInfo> <row> <ID>1</ID> <FirstName>Peter</FirstName> <LastName>The Keeper</LastName> <Club name ="NorthClub"> <ClubCoach>Pesho</ClubCoach> <ClubManager>Partan</ClubManager> <ClubEstablishedOn>1994</ClubEstablishedOn> </Club> <CityID>1</CityID> </row> <row name="Row2"> <ID>2</ID> <FirstName>Ivan</FirstName> <LastName>Mitov</LastName> <Club name = "EastClub"> <ClubCoach>Gosho</ClubCoach> <ClubManager>Goshan</ClubManager> <ClubEstablishedOn>1889</ClubEstablishedOn> </Club> <CityID>2</CityID> </row> </FootballInfo> |
Your task is to get the first <FirstName> and the second <FirstName>. As this is VBA, the libraries are a scarce resource, thus you have to code it yourself. Like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub TestingLocateXmlData() Dim xmlA As String xmlA = "<FootballInfo><row><ID>1</ID><FirstName>Peter</FirstName><LastName>The Keeper</LastName><Club name =NorthClub><ClubCoach>Pesho</ClubCoach><ClubManager>Partan</ClubManager><ClubEstablishedOn>1994</ClubEstablishedOn></Club><CityID>1</CityID></row><row name=Row2><ID>2</ID><FirstName>Ivan</FirstName><LastName>Mitov</LastName><Club name = EastClub><ClubCoach>Gosho</ClubCoach><ClubManager>Goshan</ClubManager><ClubEstablishedOn>1889</ClubEstablishedOn></Club><CityID>2</CityID></row>/FootballInfo>" Debug.Print StringBetween2Strings(xmlA, "<FirstName>", 1) 'Peter Debug.Print StringBetween2Strings(xmlA, "<LastName>", 1) 'The Keeper Debug.Print StringBetween2Strings(xmlA, "<ClubEstablishedOn>", 1) '1994 Debug.Print StringBetween2Strings(xmlA, "<ClubEstablishedOn>", 2) '1889 End Sub |
As you see, the repetition parameter is the one, on which we give the N-th wanted value. E.g., in the example above, we get 1889, if we ask for the second time the substring <ClubEstablishedOn> is found, by writing 2 as repetition parameter. The optional parameter, ending the XML is </ , but it might be changed, if needed.
This is how the function looks like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Public Function StringBetween2Strings(ByVal myText As String, _ ByVal lookBefore As String, _ ByVal repetition As Long, _ Optional ByVal lookAfter As String = "</") _ As String On Error GoTo StringBetween2Strings_Error Dim i As Long: i = 1 Dim startPosition As Long Dim endPosition As Long While repetition > 1 i = InStr(i, myText, lookBefore, vbTextCompare) myText = Right(myText, Len(myText) - i) repetition = repetition - 1 Wend startPosition = InStr(1, myText, lookBefore) + Len(lookBefore) endPosition = InStr(startPosition, myText, lookAfter, vbTextCompare) StringBetween2Strings = Mid(myText, startPosition, endPosition - startPosition) Exit Function StringBetween2Strings_Error: StringBetween2Strings = -1 End Function |
That’s all, folks. No explanation of the code. Just added it to the VBA_personal boilerplate. Enjoy!