尝试从网站导入修剪后的值

Question

Sub GetlastPrice()
Dim Html As New 
HTMLDocument, elem$, price$
Dim ws As Worksheet, URL As String
Set ws = ThisWorkbook.Worksheets("Sheet2")
With CreateObject("MSXML2.XMLHTTP")
URL = "https://www1.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=UBL"
.Open "GET", URL, False
.send
Html.body.innerHTML = .responseText
End With
elem = Html.querySelector("#responseDiv").innerText
price = Split(Split(elem, "lastPrice"":""")(1), """,")(0)
ws.Range("A2").Value = price1
End Sub

VBA 运行带来

1,020.25"}]，"optLink"："/marketinfo/sym_map/symbolMapping.jsp？symbol=UBL&instrument=-&date=-&segmentLink=17&symbolCount=2

我只想 1,020.25

请帮忙

Answer 1

确保添加对常规表达式库的支持：

步骤1：将VBA引用添加到"Microsoft VBScript正则表达式" 5.5英寸

• 选择"开发人员"选项卡(我没有此选项卡该怎么办？
• 从"代码"功能区中选择"Visual Basic"图标
• 在"Microsoft Visual Basic for Applications"窗口中，从顶部菜单中选择"工具"。
• 选择"参考资料"
• 选中"Microsoft VBScript 正则表达式 5.5"旁边的框以包含在工作簿中。
• 点击"确定">

(说明来源：Portland Runner的帖子，其中还提供了正则表达式模式语法的全面概述(

现在你可以做这样的事情：

elem = Html.querySelector("#responseDiv").innerText()
Dim strPattern As String: strPattern = """lastPrice"":""([0-9,.]+)""}]"
Dim re As New RegExp
With re
.Global = True
.MultiLine = True
.IgnoreCase = False
.Pattern = strPattern
End With
Set allMatches = re.Execute(elem)
Dim result As String: result = ""
If allMatches.Count <> 0 Then
result = allMatches.Item(0).SubMatches(0)
End If
ws.Range("A2").Value = result