CODEWITHSUNDEEP
htmlregexvbaexcelxml-parsing
Santosh
Santosh

Reputation: 12353

Regex VBA Match

How can i get the value 81.16 in second msgbox? Regex get only numeric values from string 

Sub Tests()
    Const strTest As String = "<td align=""right"">116.83<span class=""up2""></span><br>81.16<span class=""dn2""></span></td>"
    RE6 strTest
End Sub



Function RE6(strData As String) As String
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        ' .MultiLine = True
        '.Global = False
        .Pattern = "\b[\d.]+\b"
    End With

    Set REMatches = RE.Execute(strData)
    MsgBox REMatches(0)
    MsgBox REMatches(1)    'getting error here
End Function

Upvotes: 2

Views: 1502

Answers (1)

Mike
Mike

Reputation: 3015

First of all, try not to use RegEx to parse any kind of xml. Try using xml parsers or XPath

XPath, the XML Path Language, is a query language for selecting nodes from an XML document. In addition, XPath may be used to compute values (e.g., strings, numbers, or Boolean values) from the content of an XML document.

To solve your problem change

'.Global = False 'Matches only first occurrence

Into

.Global = True 'Matches all occurrences

How to properly parse XML in VBA:

Please note that I had to close your <br /> tag to be valid.

Private Sub XmlTestSub()
On Error GoTo ErrorHandler

    Dim xml As MSXML2.DOMDocument60
    Dim nodes As MSXML2.IXMLDOMNodeList
    Dim node As MSXML2.IXMLDOMNode

    yourXmlString = "<td align=""right"">116.83<span class=""up2""></span><br />81.16<span class=""dn2""></span></td>"

    Set xml = New MSXML2.DOMDocument60
    If (Not xml.LoadXML(yourXmlString)) Then
        Err.Raise xml.parseError.ErrorCode, "XmlTestSub", xml.parseError.reason
    End If

    Set nodes = xml.SelectNodes("/td/text()") 'XPath Query

    For Each node In nodes
        Debug.Print node.NodeValue
    Next node

Done:
    Exit Sub

ErrorHandler:
    MsgBox Err.Number & " " & Err.Description, vbCritical
    Resume Done

End Sub

Upvotes: 8

Related Questions