VBA-Excel: How to get text enclosed in Quotation Mark from String-CodePudding

I have a String in VBA with this text: < History Version="1.10" Client="TestClient001" >

I want to get this TestClient001 or anything that's inside Client="xxxx"

I made this code but it's not working

Client = MID(text,FIND("Client=""",text) 1,FIND("""",text)-FIND("Client=""",text)-1)

Is there a way to specifically get the text inside Client="xxxx"?

CodePudding user response：

There's no such function as Find in VBA - that's a worksheet function. The VBA equivalent is InStr, but I don't think you need to use it here.

The best tool for extracting one string from another in VBA is often Split. It takes one string and splits it into an array based on a delimiting string. The best part is that the delimiter doesn't have to be a single character - you can make it an entire string. In this case, we'd probably do well with two nested Split functions.

Client = Split(Split(text,"Client=""")(1),Chr(34))(0)

The inner Split breaks your text string where it finds "Client="". The (1) returns array element 1. Then the outer Split breaks that returned text where it finds a " character, and returns array element 0 as the final result.

For better maintainability, you may want to use constants for your delimiters as well.

Sub EnclosedTextTest()
    Const csFlag1 As String = "Client="""
    Const csFlag2 As String = """"
    Const csSource As String = "< History Version=""1.10"" Client=""TestClient001"" >"
    Dim strClient As String
    
    strClient = Split(Split(csSource, csFlag1)(1), csFlag2)(0)
    Debug.Print strClient
End Sub

However, if the Split method doesn't work for you, we can use a method similar to the one you were using, with InStr. There are a couple of options here as well.

InStr will return the position in a string that it finds a matching value. Like Split, it can be given an entire string as its delimiter; however, if you use more than one character you need to account for the fact that it will return where it finds the start of that string.

InStr(1,text,"Client=""")

will return 26, the start of the string "Client="" in the text. This is one of the places where it's helpful to have your delimiter stored in a constant.

intStart = InStr(1,text,csFlag1) len(csFlag1)

This will return the location it finds the start of the delimiter, plus the length of the delimiter, which positions you at the beginning of the text.

If you store this position in a variable, it makes the next part easier as well. You can use that position to run a second InStr and find the next occurrence of the " character.

intEnd = InStr(intStart,text,csFlag2)

With those values, you can perform your mid. You code overall will look something like this:

Sub InstrTextTest()
    Const csFlag1  As String = "Client="""
    Const csFlag2 As String = """"
    Const csSource As String = "< History Version=""1.10"" Client=""TestClient001"" >"
    
    Dim strClient As String
    Dim intPos(0 To 1) As Integer
    
    intPos(0) = InStr(1, csSource, csFlag1)   Len(csFlag1)
    intPos(1) = InStr(intPos(0), csSource, csFlag2)
    
    strClient = Mid(csSource, intPos(0), intPos(1) - intPos(0))
    Debug.Print strClient
End Sub

This will work, but I prefer the Split method for ease of reading and reuse.

CodePudding user response：

You can make use of Split function to split at character = then with last element of the resulting array remove character quotes and > with help of replace function and you will get the required output.

CodePudding user response：

In the end I got it thanks to the idea given by @alok and @Bigben

            Dim cl() As String
            Dim ClientCode As String
            
            If (InStr(1, temp, "Client=", vbTextCompare) > 0) Then
            cl = Split(temp, "=")
            ClientCode = cl(UBound(cl))
            ClientCode = Replace(ClientCode, """", "")
            ClientCode = Replace(ClientCode, ">", "")

CodePudding user response：

It's XML, so you could do this:

Dim sXML As String
sXML = "<History Version=""1.10"" Client=""TestClient001"">"
    
With CreateObject("MSXML.Domdocument")
    .LoadXML Replace(sXML, ">", "/>") 'close the element
    Debug.Print .FirstChild.Attributes.getnameditem("Client").Value
End With