正则表达式 VBA 匹配

2022-01-10 00:00:00 vba regex excel xml-parsing html

如何在第二个 msgbox 中获得值 81.16?正则表达式仅从字符串中获取数值

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 = "[d.]+"
    End With

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

推荐答案

首先，尽量不要使用RegEx解析任何类型的xml.尝试使用 xml 解析器或 XPath

XPath，XML 路径语言，是一种用于选择节点的查询语言来自 XML 文档.此外，XPath 可用于计算值(例如，字符串、数字或布尔值)来自 XML 的内容文件.

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.

为解决您的问题而改变

'.Global = False 'Matches only first occurrence

进入

.Global = True 'Matches all occurrences

如何在 VBA 中正确解析 XML:

请注意，我必须关闭您的 <br/> 标记才能有效.

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

相关文章