Copying web pages onto Excel Spreadsheets using VBA

K

kenrock

Hi,
Attempts to code, in VBA, a means of copying a web page onto an Excel
sheet have been frustrating and inconclusive.
When I apply the "QueryTables.Add" approach using the URL, I get a
partial result but it does not include the data I wish to capture -
just static data.
If I use the "FollowHyperlink" approach, Internet Explorer is called
and the web page is displayed but I cannot see how to code in VBA,
"SellectAll" and "Copy" so that I can then "Paste" to the Excel
sheet. I have tried this approach in a manual manner using a list of
Hyperlinks and calling each Hyperlink, copying the web page and
pasting onto individual spreadsheets.
Can this be achieved using VBA?
TIA, Ken Rock
 
J

Joel

Below is a simple example of getting data grom a website. If I had the URL
address and the data yo want I can write a similar routine. There are lots
of tricks in getting this data and the more experience you have the easier it
is. I have helped a number of people get web data. they all have been
succesful and happy with the resutls. The more knowledgable you are with VBA
programming and HTML format the easier it is.

Sub GetZipCodes()


ZIPCODE = InputBox("Enter 5 digit zipcode : ")

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://zip4.usps.com/zip4/citytown_zip.jsp"

'get web page
IE.Navigate2 URL
Do While IE.readyState <> 4 And _
IE.busy = True

DoEvents
Loop

Set Form = IE.document.getElementsByTagname("Form")

Set zip5 = IE.document.getElementById("zip5")
zip5.Value = ZIPCODE


Set ZipCodebutton = Form(0).onsubmit

Form(0).submit
Do While IE.busy = True
DoEvents
Loop

Set Table = IE.document.getElementsByTagname("Table")
Location = Table(0).Rows(2).innertext
IE.Quit
MsgBox ("Zip code = " & ZIPCODE & " City/State = " & Location)


End Sub
 
S

SokerGuy

I have a very similar problem to kenrock, although it's mostly because I'm
new at VBA. I'm trying to retrieve home values from zillow. I have an excel
sheet with addresses set up and want to automate this process and log the
values. My problem comes when the VBA code tries to find the home values in
the website HTML. Here is my code, any suggestions would be greatly
appreciated.

Option Explicit

Sub Get_Quotes()

Dim objIE As InternetExplorer
Dim lRow As Long
Dim abc As String
Dim striEst As String
Dim ele As Object

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = True
.navigate "http://www.zillow.com/"

For lRow = 2 To Range("A" & Rows.Count).End(xlUp).Row

Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop

.document.getElementsByName("citystatezip").Item(0).Value =
Sheets("MAIN").Range("D" & lRow)
.document.getElementById("GOButton").Click

Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop

Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop

Do While .Busy: DoEvents: Loop
Do While .readyState <> 4: DoEvents: Loop

For Each ele In objIE.document.getElementsByTagName("span")
If ele.class = "price" Then abc = ele.innerText: Exit For
Next

Sheets("MAIN").Range("E" & lRow) = abc

Next lRow

End With

Set objIE = Nothing

End Sub
 
J

Joel

This will help you along. I did a dump on sheet 1 of all the properties on
the webstire. From this dump I was able to get some of the info. didn't
have time to figure out how to get everything. If you need more help let me
know. this willget you moving along

Sub Get_Quotes()

'Dim objIE As InternetExplorer
Dim lRow As Long
Dim abc As String
Dim striEst As String
Dim ele As Object

myzip = "10001"
Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = True
.navigate "http://www.zillow.com/"

For lRow = 2 To 2

Do While .Busy Or _
.readyState <> 4

DoEvents
Loop

Set citystate = .document.getElementsByName("citystatezip")
citystate.Item(0).Value = myzip
.document.getElementById("GOButton").Click

Do While .Busy Or _
.readyState <> 4

DoEvents
Loop
Set sht = Sheets("Sheet1")
RowCount = 1
For Each itm In .document.all
sht.Range("A" & RowCount) = itm.tagname
sht.Range("B" & RowCount) = itm.classname
sht.Range("C" & RowCount) = itm.ID
sht.Range("D" & RowCount) = Left(itm.innertext, 1024)

RowCount = RowCount + 1
Next itm


Set sht = Sheets("Sheet2")
RowCount = 1
sht.Range("A" & RowCount) = "adr"
sht.Range("B" & RowCount) = "listing"
sht.Range("C" & RowCount) = "type type-forSale"
sht.Range("D" & RowCount) = "type"
sht.Range("E" & RowCount) = "price"

RowCount = 2

For Each ele In .document.all

Select Case ele.classname
Case "property-info"
RowCount = RowCount + 1
Case "adr"
sht.Range("A" & RowCount) = ele.innertext
Case "listing"
sht.Range("B" & RowCount) = ele.innertext
Case "type type-forSale"
sht.Range("C" & RowCount) = ele.innertext
Case "type"
sht.Range("D" & RowCount) = ele.innertext
Case "price"
sht.Range("E" & RowCount) = ele.innertext
End Select
Next ele


Next lRow

End With

Set objIE = Nothing

End Sub
 
S

SokerGuy

Great! This is very helpful. Thank you so much. I have one last question
if you have a minute. The info being copied over to Sheet2 from the data
dump seems to write over itself each time a new address is entered. It
doesn't write to row2, then row3, then row4,... as the process continues down
the original address list (since I have an excel list of address I'm trying
to automate).

Thanks again.
 
J

Joel

the code was only meant to get you on the right track. I didn't download
every description for each house. Only the ones I found with the first
listing. You have to add the addional descriptions yourself. Here is the
code so it wouldn't over-write with each new listing.

the code isn't getting each house for a zip code. It seem the page is only
getting the 1st 24 houses for each zip code.


Sub Get_Quotes()

'Dim objIE As InternetExplorer
Dim lRow As Long
Dim abc As String
Dim striEst As String
Dim ele As Object

Set sht = Sheets("Sheet2")
RowCount = 1
sht.Range("A" & RowCount) = "adr"
sht.Range("B" & RowCount) = "listing"
sht.Range("C" & RowCount) = "type type-forSale"
sht.Range("D" & RowCount) = "type"
sht.Range("E" & RowCount) = "price"

RowCount = 2

myzip = "10001"
Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = True
.navigate "http://www.zillow.com/"

For lRow = 2 To 2

Do While .Busy Or _
.readyState <> 4

DoEvents
Loop

Set citystate = .document.getElementsByName("citystatezip")
citystate.Item(0).Value = myzip
.document.getElementById("GOButton").Click

Do While .Busy Or _
.readyState <> 4

DoEvents
Loop


For Each ele In .document.all

Select Case ele.classname
Case "property-info"
RowCount = RowCount + 1
Case "adr"
sht.Range("A" & RowCount) = ele.innertext
Case "listing"
sht.Range("B" & RowCount) = ele.innertext
Case "type type-forSale"
sht.Range("C" & RowCount) = ele.innertext
Case "type"
sht.Range("D" & RowCount) = ele.innertext
Case "price"
sht.Range("E" & RowCount) = ele.innertext
End Select
Next ele

Next lRow

End With

Set objIE = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to write a VBA program to load the webpages automatically 1
Update MySQL database using Excel VBA 1
Save as Text 1
excel 2007 add-ins and other VBA hidden files. General information 2
copying VBA from one Excel file to another 1
Copy/close web pages 3
putting a picture onto a userform without using the vba window. 11
Copying and pasting from Excel to Powerpoint using VBA 1

Top