CODEWITHSUNDEEP
vbaexcelinternet-explorer
Mike
Mike

Reputation: 155

Open website, find specific value and return value to Excel in VBA

I would like to use VBA to open a website, look for a certain paragraph in the HTML code of this website (<p class="myClass">XYZ</p>) and return this value to Excel, in my example "XYZ".

The website has only one paragraph (p element) with the above class.

I know that this is possible but don't know where to start here.

My code:

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
    .Navigate varUrl
    Do While .Busy
          Application.Wait Now + TimeValue("0:00:01")
    Loop
    .Visible = True
End With

Upvotes: 1

Views: 5042

Answers (3)

Matt Spinks
Matt Spinks

Reputation: 6698

Instead of opening IE, use a web request:

Set oRequest = New WinHttp.WinHttpRequest
With oRequest
    .Open "GET", sUrl, True
    .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
    .Send "{range:9129370}"
    .WaitForResponse
    Set index = .ResponseText.IndexOf("<p class=""myClass"">")
    Set text = .ResponseText.Substring(index,3)
    Cells(row, col).Value = text
End With

If you don't know the length of the string you are looking for, you could also do a loop after index until you hit a "<" character.

Upvotes: 2

Vityata
Vityata

Reputation: 43575

That is a tricky and interesting question. Let's say that you want to obtain the title of this current website, which is in class question-hyperlink within StackOverflow. Thus, using the idea of the solution of @Matt Spinks you may come up with something like this:

Option Explicit

Public Sub TestMe()

    Dim oRequest    As Object
    Dim strOb       As String
    Dim strInfo     As String: strInfo = "class=""question-hyperlink"">"
    Dim lngStart    As Long
    Dim lngEnd      As Long

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

    With oRequest
        .Open "GET", "http://stackoverflow.com/questions/42254051/vba-open-website-find-specific-value-and-return-value-to-excel#42254254", True
        .SetRequestHeader "Content-Type", "application/x-www-form-urlencoded; charset=UTF-8"
        .Send "{range:9129370}"
        .WaitForResponse
        strOb = .ResponseText

    End With

    lngStart = InStr(1, strOb, strInfo)
    lngEnd = InStr(lngStart, strOb, "<")

    Debug.Print Mid(strOb, lngStart + Len(strInfo), lngEnd - lngStart - Len(strInfo))

End Sub

Instead of Debug.print you may get the Title in a string and work further.

Upvotes: 0

pokemon_Man
pokemon_Man

Reputation: 902

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
    .Navigate varUrl
    Do While .Busy
          Application.Wait Now + TimeValue("0:00:01")
    Loop
    .Visible = True
End With

'HTML document
Dim doc As Object
Set doc = objIE.document

Dim el As Object
Dim myText as string
For Each el In doc.GetElementsByClassName("myClass")
    'put paragrah text in cell A1
    Cells(1, 1).Value = el.innerText

    'put your paragraph text in a variable string
    myText = el.innerText
Next el

Upvotes: 1

Related Questions