Error on VBA code for pull or extract data from HTML Element in Excel

Hello everyone
I try to to extract or get data from HTML Element in Excel using VBA code as per attached file named "VBA code - Extract Data from HTML web".
My idea is to extract the exchange rate data in red highlight from the HTML website as below quote and unquote:

Quote//
SG : 1.3006 SGD = 100 JPY 2020-06-15 MAS noon buying
100 Japanese Yen buys 1.3006 Singapore Dollars
SG : 1.3001 SGD = 100 JPY 2020-06-12 MAS noon buying
100 Japanese Yen buys 1.3001 Singapore Dollars
SG : 1.2986 SGD = 100 JPY 2020-06-11 MAS noon buying
100 Japanese Yen buys 1.2986 Singapore Dollars
SG : 1.2887 SGD = 100 JPY 2020-06-10 MAS noon buying
100 Japanese Yen buys 1.2887 Singapore Dollars
SG : 1.2857 SGD = 100 JPY 2020-06-09 MAS noon buying
100 Japanese Yen buys 1.2857 Singapore Dollars
SG : 1.2726 SGD = 100 JPY 2020-06-08 MAS noon buying
100 Japanese Yen buys 1.2726 Singapore Dollars
SG : 1.2773 SGD = 100 JPY 2020-06-05 MAS noon buying
100 Japanese Yen buys 1.2773 Singapore Dollars

Unquote//

Unfortunately, when i try to run the code, it compile the error as "run-time error 445" and "object doesn't support this action"

Appreciate someone can guide me to find out what is the error.

Did you consider using API ..?
Try the following code

Sub Test()
    Const sURL As String = "https://eservices.mas.gov.sg/api/action/datastore/search.json?resource_id=95932927-c8bc-4e7a-b484-68a66a24edfe&fields=end_of_day,jpy_sgd_100&limit=10&sort=end_of_day%20desc"
    Dim a, ws As Worksheet, json As Object, colData As Collection, m As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set json = GetJSON(sURL)
    Set colData = json("result")("records")
    a = CollectionToArray(colData)
    With ws
        .Cells.ClearContents
        .Range("A1").Resize(1, 2).Value = Array("End Of Day", "Amount")
        .Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    End With
    Set json = Nothing: Set colData = Nothing
End Sub

Function GetJSON(ByVal sURL As String) As Object
    Dim http As MSXML2.XMLHTTP60
    Set http = New MSXML2.XMLHTTP60
    With http
        .Open "Get", sURL, False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        Set GetJSON = JSONConverter.ParseJson(.responseText)
    End With
End Function

Function CollectionToArray(c As Collection) As Variant()
    Dim a(), i As Long, j As Long
    ReDim a(1 To c.Count, 1 To 2)
    For i = 1 To c.Count
        a(i, 1) = c.Item(i)("end_of_day")
        a(i, 2) = c.Item(i)("jpy_sgd_100")
    Next i
    CollectionToArray = a
End Function

YasserKhalil, thank you very much for your help.
I try to run the code as given but it is compile error "Run time error "424":Object require" on Set GetJSON = JSONConverter.ParseJson(.responseText).

Hello
Sorry I forgot to mention about that point ..

Go to the following link
https://github.com/VBA-tools/VBA-JSO...nConverter.bas
Copy the whole code and put it in Stadard Module and name it "JSONConverter"

Tool > References: Microsoft Scritpting Runtime

Have a look at the attachment but you have to learn how to do such stuff by yourself

Cross-posted at:
https://chandoo.org/forum/threads/er...n-excel.44486/
http://www.vbaexpress.com/forum/show...-HTML-Element)
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html