How to retrieve data from a website using VBA

rca

Board Regular
Joined
Mar 1, 2005
Messages
182
I have the following code:

Code:
Sub RetrieveData()
 
    'dimension (set aside memory for) our variables
    Dim objIE As InternetExplorer
    Dim ele As Object
    Dim y As Integer
 
    'start a new browser instance
    Set objIE = New InternetExplorer
    'make browser visible
    objIE.Visible = True
 
    'navigate to page with needed data
    objIE.navigate "http://www.wsj.com/mdc/public/page/2_3020-treasury.html"
    'wait for page to load
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
 
    'we will output data to excel, starting on row 1
    y = 1
 
    'look at all the 'tr' elements in the table,
    'and evaluate each, one at a time, using 'ele' variable
    For Each ele In objIE.document.getElementById("mdcTable").getElementsByTagName("tr")
        'show the text content of 'tr' element being looked at
        Debug.Print ele.textContent
        'each 'tr' (table row) element contains 4 children ('td') elements
        'put text of 1st 'td' in col A
        Sheets("Data").Range("A" & y).Value = ele.Children(0).textContent
        'put text of 2nd 'td' in col B
        Sheets("Data").Range("B" & y).Value = ele.Children(1).textContent
        'put text of 3rd 'td' in col C
        Sheets("Data").Range("C" & y).Value = ele.Children(2).textContent
        'put text of 4th 'td' in col D
        Sheets("Data").Range("D" & y).Value = ele.Children(3).textContent
        'increment row counter by 1
        y = y + 1
    'repeat until last ele has been evaluated
    Next
 
 
End Sub

I'm seeing an error message that says: Run time error 424: Object required.

on this line: For Each ele In objIE.document.getElementById("mdcTable").getElementsByTagName("tr")

Do I have the wrong .getElementById? Thanks!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Unfortunately, mdcTable is the class name, not the id. Try looping through each table in the document to find the one where the first cell in the header row contains Maturity. Then, once found, loop through each row within that table...

Code:
[FONT=Courier New]    [COLOR=darkblue]Dim[/COLOR] HTMLTable [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] HTMLTable [COLOR=darkblue]In[/COLOR] objIE.document.getElementsByTagName("table")
        [COLOR=darkblue]If[/COLOR] HTMLTable.rows(0).cells(0).innerText = "Maturity" [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] HTMLTable
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] HTMLTable [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] ele [COLOR=darkblue]In[/COLOR] HTMLTable.getElementsByTagName("tr")
            [COLOR=green]'etc[/COLOR]
            '
            [COLOR=green]'[/COLOR]
        [COLOR=darkblue]Next[/COLOR] ele
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "The table wasn't found.", vbInformation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR][/FONT]

Hope this helps!
 
Upvote 0
Thanks, Domenic. VBA is not my specialty. Where does your code block fit within the code that I provided? What goes in the "etc" lines in the code that you provided?
 
Upvote 0
I haven't tested it, but you would have the following...

Code:
Sub RetrieveData()
 
    'dimension (set aside memory for) our variables
    Dim objIE As InternetExplorer
    [COLOR=#ff0000]Dim HTMLTable As Object[/COLOR]
    Dim ele As Object
    Dim y As Integer
 
    'start a new browser instance
    Set objIE = New InternetExplorer
    'make browser visible
    objIE.Visible = True
 
    'navigate to page with needed data
    objIE.navigate "http://www.wsj.com/mdc/public/page/2_3020-treasury.html"
    'wait for page to load
    Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop
    
    [COLOR=#ff0000]For Each HTMLTable In objIE.document.getElementsByTagName("table")
        If HTMLTable.Rows(0).Cells(0).innerText = "Maturity" Then
            Exit For
        End If
    Next HTMLTable[/COLOR]
    
    [COLOR=#ff0000]If Not HTMLTable Is Nothing Then[/COLOR]
        'we will output data to excel, starting on row 1
        y = 1
        'look at all the 'tr' elements in the table,
        'and evaluate each, one at a time, using 'ele' variable
       [COLOR=#ff0000] For Each ele In HTMLTable.getElementsByTagName("tr")[/COLOR]
            'show the text content of 'tr' element being looked at
            Debug.Print ele.textContent
            'each 'tr' (table row) element contains 4 children ('td') elements
            'put text of 1st 'td' in col A
            Sheets("Data").Range("A" & y).Value = ele.Children(0).textContent
            'put text of 2nd 'td' in col B
            Sheets("Data").Range("B" & y).Value = ele.Children(1).textContent
            'put text of 3rd 'td' in col C
            Sheets("Data").Range("C" & y).Value = ele.Children(2).textContent
            'put text of 4th 'td' in col D
            Sheets("Data").Range("D" & y).Value = ele.Children(3).textContent
            'increment row counter by 1
            y = y + 1
        'repeat until last ele has been evaluated
        Next ele
    [COLOR=#ff0000]Else
        MsgBox "The table wasn't found.", vbInformation
    End If[/COLOR]
 
End Sub
 
  • Like
Reactions: rca
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top