Downloading a data from a web page and pasting it on an excel Sheet

[Mayur Gwalani]

I am brand new to the world of macros and visual basic, so bear with me . I want to create a macro that will automatically pull data from the web by simply pressing a macro button. I assume that this would be an easy task if the data is from a website with a specific URL such as [url]http://192.168.2.18:8889/ameyoreports/?acpMode=false#listRunnableReport. Any help with this would be greatly appreciated.[/URL] <tbody> </tbody>

Is something like this even possible?

Thanks in advance for your help!

<colgroup><col></colgroup><tbody>
</tbody>

 

[Haluk]

Try this;

Sub Test()
    Dim URL As String
    Dim IE As Object
    Dim HTML_Body As Object, HTML_Tables As Object, MyTable As Object
    URL = "http://web1.ncaa.org/d1mfb/Internet/national rankings/IA_teamrush.html"

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate URL
    Do Until IE.ReadyState = 4: DoEvents: Loop
    
    Set HTML_Body = IE.document.GetElementsByTagName("Body").Item(0)
    Set HTML_Tables = HTML_Body.GetElementsByTagName("Table")
    Set MyTable = HTML_Tables(2)
    Set HTML_TableRows = MyTable.GetElementsByTagName("Tr")
    
    For Each MyRow In HTML_TableRows
        j = 3
        i = i + 1
        Set HTML_TableDivisions = MyRow.GetElementsByTagName("Td")
        For Each Td In HTML_TableDivisions
            j = j + 1
            RetVal = Td.InnerText
            Cells(i, j) = RetVal
        Next
    Next
    
    Set HTML_Body = Nothing
    Set HTML_Tables = Nothing
    Set MyTable = Nothing
    Set IE = Nothing
End Sub

 

[Haluk]

This code will format the sheet better;

Sub Test()
    Dim URL As String
    Dim IE As Object
    Dim HTML_Body As Object, HTML_Tables As Object, MyTable As Object
    URL = "http://web1.ncaa.org/d1mfb/Internet/national rankings/IA_teamrush.html"

    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate URL
    Do Until IE.ReadyState = 4: DoEvents: Loop
    
    Set HTML_Body = IE.document.GetElementsByTagName("Body").Item(0)
    Set HTML_Tables = HTML_Body.GetElementsByTagName("Table")
    
    Set MyTable = HTML_Tables(1)
    Range("A1") = MyTable.Rows(0).Cells(0).innertext
    Range("B1") = MyTable.Rows(0).Cells(1).innertext
   
    Set MyTable = HTML_Tables(2)
    For Z = 4 To 14
        Cells(1, Z) = MyTable.Rows(0).Cells(Z - 4).innertext
    Next
    
    Range("A1:N1").Font.Bold = True
    
    Range("A1:N1").Font.ColorIndex = 3
    Range("A1:B1").Font.ColorIndex = 5
    
    Set HTML_TableRows = MyTable.GetElementsByTagName("Tr")
    
    For Each MyRow In HTML_TableRows
        j = 3
        i = i + 1
        Set HTML_TableDivisions = MyRow.GetElementsByTagName("Td")
        For Each Td In HTML_TableDivisions
            j = j + 1
            RetVal = Td.innertext
            Cells(i, j) = RetVal
        Next
    Next
    
    Range("A:N").Columns.AutoFit
    
    Set HTML_Body = Nothing
    Set HTML_Tables = Nothing
    Set MyTable = Nothing
    Set IE = Nothing
End Sub