CODEWITHSUNDEEP
htmlexcelvba
Michael McCarthy
Michael McCarthy

Reputation: 3

How do I pull value from external website by Element ID with Excel / VBA?

I'm trying to retrieve values from external websites by element ID using VBA and add them to my excel table. The website URL's are indicated in column A. Column B and C are for my retrieved values.

URL example Element ID name: "youtube-user-page-country"

Excel Pic

Bellow is my poor attempt:

Sub getCountry()

 Dim IE As New InternetExplorer
 IE.Visible = False

 IE.navigate Worksheets("Sheet1").Range(A3).Value
 Do
 DoEvents
 Loop Until IE.readyState = READYSTATE_COMPLETE

 Dim Doc As HTMLDocument
 Set Doc = IE.document

Dim getCountry As String

getCountry = Trim(Doc.getElementsByTagName("youtube-user-page-country").innerText)

Worksheets("Sheet1").Range(B31).Value = getCountry

End Sub

The code isn't working showing problems with object definition. Could anyone give me tips on where I'm going wrong?

I've been a macro recorder user and the switch has quite a steep learning curve :-)

Thanks for any help !

Upvotes: 0

Views: 2334

Answers (2)

ASH
ASH

Reputation: 20302

You can use this to dump the data to your spreadsheet.

Sub DumpData()

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://finance.yahoo.com/q?s=sbux&ql=1"

'Wait for site to fully load
IE.Navigate2 URL
Do While IE.Busy = True
   DoEvents
Loop

RowCount = 1

With Sheets("Sheet1")
   .Cells.ClearContents
   RowCount = 1
   For Each itm In IE.document.all
      .Range("A" & RowCount) = itm.tagname
      .Range("B" & RowCount) = itm.ID
      .Range("C" & RowCount) = itm.classname
      .Range("D" & RowCount) = Left(itm.innertext, 1024)

      RowCount = RowCount + 1
   Next itm
End With
End Sub

Thanks Joel!!!

Upvotes: 0

Ryan Wildry
Ryan Wildry

Reputation: 5677

I think I get what you are after. There were a few issues:

  1. You want to use getElementByID.
  2. Naming a string getCountry and the SubRoutine getCountry containing it is not a good idea. You can do it, but don't.
  3. Always fully qualify your sheet references so you know what workbook and sheet you are working with

Here's the revised code, I have it working on my end.

Sub getCountry()
    Dim IE      As Object: Set IE = CreateObject("InternetExplorer.Application")
    Dim ws      As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim Country As String

    With IE
        .Visible = False
        .navigate ws.Range("A3").Value

        Do
            DoEvents
        Loop Until .readyState = 4

    End With

    Country = Trim$(IE.document.getElementByID("youtube-user-page-country").innerText)
    ws.Range("B31").Value2 = Country
    IE.Quit
End Sub

Upvotes: 1

Related Questions