How to retrieve data from a website using VBA

[rca]

I have the following 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!

 

[Domenic]

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...

[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!

 

[rca]

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?

 

[Domenic]

I haven't tested it, but you would have the following...

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

 

[rca]

This solution works perfectly. Thank you, Domenic.