Discuss "Retrieving Web Data using Excel" in the Microsoft Office forum.

mehere · #1 March 25th, 2008, 09:52 AM

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?

jmurrayhead · #2 March 25th, 2008, 10:10 AM

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.

richyrich · #3 March 25th, 2008, 10:35 AM

Does the webpage just contain data or is there lots of other HTML info?

Could you give an example of how the page is displayed and/or how the data is formatted?

mehere · #4 March 25th, 2008, 10:39 AM

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.

richyrich · #5 March 25th, 2008, 10:42 AM

And you want the details of the "Local Agents" down the left hand side?

mehere · #6 March 25th, 2008, 10:45 AM

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

jmurrayhead · #7 March 25th, 2008, 02:12 PM

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.

mehere · #8 March 25th, 2008, 02:16 PM

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

jmurrayhead · #9 March 25th, 2008, 02:17 PM

Quote:

Originally Posted by mehere

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

Yes.

mehere · #10 March 25th, 2008, 02:45 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.developerbarn.com/microsoft-office/68-retrieving-web-data-using-excel.html
Posted By	For	Type	Date
DeveloperBarn Forums - ASP Help, ASP.Net Help, PHP Help, SQL Help, Tutorials, Windows Help	This thread	Refback	April 26th, 2008 09:37 AM

Discuss "Retrieving Web Data using Excel" in the Microsoft Office forum.

Microsoft Office - Learn helpful tips and tricks to get the best out of Office suite, using Excel, Word, PowerPoint and more. Discuss how to automate repetitive tasks and more.