CODEWITHSUNDEEP
excelvbahtmlhtml-object
Vamsi
Vamsi

Reputation: 732

How to grab a portion of a website text into an Excel cell

I'm trying to automate creating a list of descriptions from the GM Parts website for a range of part number values.

For example, the following is the link for the Part number 23498355 - http://www.gmpartsdirect.com/oe-gm/23498355

I'm trying to grab the Part description text "This ABS Sensor is a genuine OEM GM part #23498355 and carries a factory warranty. We offer the best online prices with fast shipping on any order placed with us." available on this webpage into Excel.

I wrote the following code to get that info, but couldn't complete the last few lines which can grab this specific info.

Option Explicit

Sub myConnection()
    Dim oHtml, myData, Title, cste
    Set oHtml = New HTMLDocument
    With CreateObject("WINHTTP.WinHTTPRequest.5.1")
        .Open "GET", "http://www.gmpartsdirect.com/oe-gm/23498355", False
        .send
        oHtml.body.innerHTML = .responseText
    End With
'Rest of the code to grab the exact part description
End Sub

Once I get this working, the idea is to automate the process for a list of part numbers. Can anyone help me with completing this piece of code?

Upvotes: 0

Views: 243

Answers (1)

Tim Williams
Tim Williams

Reputation: 166146

Using MSHTML to parse your HTML is a bit limited, since many "modern" document methods might not be implemented, but you can make it work in this case:

Sub myConnection()
    Dim oHtml, myData, Title, cste, d
    Set oHtml = New MSHTML.HTMLDocument


    With CreateObject("WINHTTP.WinHTTPRequest.5.1")
        .Open "GET", "http://www.gmpartsdirect.com/oe-gm/23498355", False
        .send
        oHtml.body.innerHTML = .responseText

        Set d = myGetElementsByClassName(oHtml, "div", "description_body")
        If Not d Is Nothing Then
            Debug.Print d.innerText
        End If

    End With
'Rest of the code to grab the exact part description
End Sub


'return an element given its tag name and class name
Function myGetElementsByClassName(doc, tagName, className) As Object
    Dim el As Object
    For Each el In doc.getElementsByTagName(tagName)
        If el.className = className Then
            Set myGetElementsByClassName = el
            Exit Function
        End If
    Next el
    Set myGetElementsByClassName = Nothing
End Function

Upvotes: 1

Related Questions