Thread: Retrieving Web Data using Excel

I have an excel program that will retrieve data from websites and place it into an excel spreadhseet ... however, it only works with table data. The sites I need to retrieve the data from are using DIV tags and i'm finding it hard to just grab that info.

The excel module uses the following:

Code:

Sub ImportDataWeb()
    
    Sheets("Website Data").Activate
    i = 1
    Do Until Sheets("Hyperlinks Here").Cells(i, 2) = ""
        myquery = Sheets("Hyperlinks Here").Cells(i, 2)
        Sheets("Website Data").Cells(1, 1) = myquery
        myrow = Sheets("Website Data").UsedRange.Rows.Count + 1
        Do
            myrow = myrow - 1
        Loop Until Sheets("Website Data").Cells(myrow, 1) <> ""
        myrow = myrow + 3
        With Sheets(3).QueryTables.Add(Connection:= _
            "URL;" & myquery, Destination:=Sheets(3).Cells(myrow, 1))
            .BackgroundQuery = True
            .TablesOnlyFromHTML = True
            .Refresh BackgroundQuery:=False
            .SaveData = True
        End With
        i = i + 1
    Loop
    
End Sub

with the code TablesOnlyFromHTML, it will grab the data in the table and place it where I need it. However, if I use False, it grabs all the data on the page and with that, it's hard to get just what I need.

Anyone have any ideas?

I'm not sure what you're asking can be done very easily. It seems you would have to create a function to actually parse through the HTML and retrieve the information you want. If they had an XML file that was available which contains this data, that would be another story. That would be quite simple.

here's a url of an example page i need to get the data from. i would have to gather the name, address, phone & ins co name(s) from this page. this data then needs to go into something like a .CSV file for later import into a SQL Table.

click here. this is not the page i need to scrub, just and example of what i'm looking to do.

yes ... for example
name: Joseph Kaplan
address: 617 New York Ave, Absecon, NJ 08201
phone: (609) 645-8543
ins co: High Point
etc....

mehere, perhaps this is what you're looking for:

Code:

Set IE = CreateObject("InternetExplorer.Application")
IE.navigate "http://www.yahoo.com" 'Place site URL here
IE.Visible = False 'Does not display the browser window
 
While IE.Busy
    DoEvents
Wend
 
x = IE.document.body.innerHTML
IE.Quit

In the above code, html in the body tag will be stored in the variable 'x'. If each div has an ID, I would imagine you could somehow access it this way.

will look into that ... is this VBA or what?

Originally Posted by mehere

will look into that ... is this VBA or what?

Yes.

yes it's VBA? or yes it's what? ...