CODEWITHSUNDEEP
vbaexcelexcel-formulaexport-to-excel
Narasappa
Narasappa

Reputation: 556

Get website data from Urls using VBA

I have multiple urls stored in Excel sheet. I want to Get data reside within particular div tag. For One Website it works fine 

Sub Cityline()

Dim IE As Object

Set IE = CreateObject("Internetexplorer.application")

IE.Visible = True

IE.navigate "http://Someurl.com/bla/bla/bla"

Do While IE.busy

DoEvents

Loop

Do

DoEvents

Dim Doc As Object

Set Doc = IE.Document

Dim workout As String

workout = Doc.getElementsByClassName("CLASS_NAME_OF_DATA")(0).innertext

Range("A2") = workout

Loop

End Sub

I used Below code for loop Through all urls but its not working

Sub GetData() 
    Dim oHtm As Object: Set oHtm = CreateObject("HTMLFile") 
    Dim req As Object: Set req = CreateObject("msxml2.xmlhttp") 
     Dim oRow As Object 
     Dim oCell As Range 
    Dim url As String 
   Dim y As Long, x As Long 

x = 1 
For Each oCell In Sheets("sheet1").Range("A2:A340") 
    req.Open "GET", oCell.Offset(, 1).Value, False 
    req.send 
    With oHtm 
        .body.innerhtml = req.responsetext 
        With .getelementsbytagname("table")(1) 
            With Sheets(1) 
                .Cells(x, 1).Value = oCell.Offset(, -1).Value 
                .Cells(x, 2).Value = oCell.Value 
            End With 
            y = 3 
            For Each oRow In .Rows 
                Sheets(1).Cells(x, y).Value = oRow.Cells(1).innertext 
                y = y + 1 
            Next oRow 
        End With 
    End With 
    x = x + 1 
Next oCell

End Sub

But its not working can any one suggest me where i went wrong ?

I used Fetching Data from multiple URLs but it doesn't works for me. Please guide me how to get data from all urls at a Time

Upvotes: 0

Views: 4851

Answers (1)

iambasil
iambasil

Reputation: 105

I'm new to SO, so apologies to the mods if this should be in comments (I couldn't get it to fit).

I agree with Silver's comments, but I thought I'd suggest a different approach that might help. If you have URLs in a column of cells, you could create a custom VBA function that will extract the relevant data out of the HTML. Just use this function in the cells to the right of your URL to return the relevant data from the HTML. An example is this:

Public Function GetHTMLData(SiteURL As String, FieldSearch As String) As String
Dim IE As Object
Dim BodyHTML As String
Dim FieldStart As Integer
Dim FieldEnd As Integer

Set IE = CreateObject("InternetExplorer.Application")

With IE
.Navigate SiteURL
Do While .Busy Or .ReadyState <> 4
DoEvents
Loop

BodyHTML = IIf(StrComp(.Document.Title, "Cannot find server", vbTextCompare) = 0, _
    vbNullString, .Document.body.innerhtml)
FieldStart = InStr(1, BodyHTML, FieldSearch) + Len(FieldSearch) + 12
FieldEnd = InStr(FieldStart, BodyHTML, "<")

GetHTMLData = Mid(BodyHTML, FieldStart, FieldEnd - FieldStart)
.Quit
End With

Set IE = Nothing
End Function

The function above has 2 input parameters: the URL and a string that will be searched for within the HTML. It will then return a string from within the HTML, starting from 12 characters after the searched parameter and ending at the following '<' within the HTML.

Hope that helps.

Upvotes: 0

Related Questions