Parse line from HTML response in Excel VBA

Question

I am looking to parse a 'WinHttpRequest' response in Excel VBA to pull a specific line from the HTML response. Here is the code for the HTTP request.

Function getSetName(ByVal setNUMBER As String) As String
    Dim oRequest As Object
    Dim xmlResponse As String
    Dim setDescription As String
    Dim setName As String
   

    Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")

    oRequest.Open "GET", "https://brickset.com/sets/75192" '& setNUMBER
    oRequest.Send
    xmlResponse = oRequest.ResponseText
    
    'parse xml response here
    
    getSetName = setName

End Function

I am looking to parse only the line with the HTML tag 'meta name=""description""' to a string which I will later pull information from.

Any help or direction on how to parse this single line would be appreciated.

There are plenty of posts here on SO (and elsewhere) covering parsing XM. E.g: https://stackoverflow.com/questions/11305/how-to-parse-xml-using-vba?rq=1 Are you really parsing XML though, or HTML? — Tim Williams, Nov 29 '21 at 18:11

score 1 · Accepted Answer · answered Nov 29 '21 at 18:39

1

Try

Sub Test_GetSetName()
    Debug.Print GetSetName("75192")
End Sub

Function GetSetName(ByVal SetNUMBER As String) As String
    Dim html As New MSHTML.HTMLDocument
    With CreateObject("WinHttp.WinHttpRequest.5.1")
        .Open "GET", "https://brickset.com/sets/" & SetNUMBER, False
        .send
        html.body.innerHTML = .responseText
    End With
    GetSetName = html.querySelector("[name='description']").Content
End Function

answered Nov 29 '21 at 18:39

YasserKhalil

9,138
7
36
95

1

Thanks for the help, just needed to make sure the "Microsoft HTML Object Library" was also selected under Tool > References. – afskymonkey Nov 29 '21 at 20:13

Parse line from HTML response in Excel VBA

1 Answers1