Trying to import trimmed value from website

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

The vba run brings

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

I just wanna 1,020.25

Please help

Replace `""","` with `Chr(34)` – Scott Craner Jun 19 '20 at 18:22 — Scott Craner, Jun 19 '20 at 18:22
Excellent. Now there is no problem – Jugal Kishor Jul 25 '20 at 04:11 — Jugal Kishor, Jul 25 '20 at 04:11

score 0 · Accepted Answer · answered Jun 19 '20 at 19:28

Make sure to add support for the Regular Expresison library:

Step 1: Add VBA reference to "Microsoft VBScript Regular Expressions 5.5"

Select "Developer" tab (I don't have this tab what do I do?)

Select "Visual Basic" icon from 'Code' ribbon section

In "Microsoft Visual Basic for Applications" window select "Tools" from the top menu.

Select "References"

Check the box next to "Microsoft VBScript Regular Expressions 5.5" to include in your workbook.

Click "OK"

(Instructions credited to: Portland Runner's post, which also provides a comprehensive overview of the regexp pattern syntax)

Now you can do something like this:

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

Trying to import trimmed value from website

1 Answers1