Download Web Data to excel

[Joel]

I buying a new car and the lcoal dealer wants to charge me to shipped a car
from another dealer. I'm trying to find the dealer who has the car. So I
want to get a list of all the car dealers in my area and then search each
inventory on the web.

I simply go to the Nissan wepage setting the zipcode. The page only returns
4 results. I then change the distance box in code to 50 miles and press the
Search button in code which returns 55 results. I'm have to manually break
the code because I can't get the code to wait for the Search to complete. I
also can get the 1st 10 reults, but the other results are on other pages and
can't find the data. Right now I'm trying to change the Sheet box from sheet
1 to sheet 2.

The webpage is writen in Java and I don't know Java, but do know Excel Macro
very well. Not sure if this is best way of getting data but have some code
that works. Having two problems as documented in the code

1) I can't get the code to wait for a Java script to complete. The code
does work when a webpage is loading.

2) I can only get the 1st 10 returned item out of 55 items. The rest of the
items are on additional pages and can't change sheets.



Sub GetDealers()
URL = "http://www.nissanusa.com/apps/dealerlocator"
Request = "?zipCode=07508&tool=Home.Locator"

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

'get web page
IE.Navigate2 URL & Request
Do While IE.readystate <> 4
DoEvents
Loop

'get search button
Set but = IE.document.getElementById("mainSearchButton")
'put distance in listbox on webpage
Set radius = IE.document.getElementById("radius")
radius.Value = "50"


'search again a larger distance
'Select Search button and activate
but.Select
but.Click
Set SearchResults = IE.document.getElementById("searchResults")
Do While IE.busy = True
DoEvents
Loop
'-------------------------------------------------------
' Cant get code to wait here for data to be retrieved.
'-------------------------------------------------------


Set SearchResultsCount = IE.document.getElementById("searchResultsCount")

With Sheets("Sheet1")
RowCount = 1
For Each Chld In SearchResults.Children
If Chld.innertext = "" Then
Exit For
End If
.Range("A" & RowCount) = Chld.innertext
RowCount = RowCount + 1
Next Chld
End With

'-------------------------------------------------------
' Can't get all pages of data. Only gets 1st 10 items.
'-------------------------------------------------------



'-------------------------------------------------------
' Other tags not used
'-------------------------------------------------------

Set pageTags = IE.document.getElementById("pageTags")
Set pagebg = IE.document.getElementById("pagebg")
Set cdpageContent = IE.document.getElementById("cdpageContent")
Set but2 = IE.document.getElementById("SearchButton")
Set allTabs = IE.document.getelementsbytagname("TABLE")
Set but2 = IE.document.getElementById("SearchButton")
Set crmEngine = IE.document.getElementById("crmEngine")
Set gBody = IE.document.getElementById("gBody")
Set movFlashGlobalNav = IE.document.getElementById("movFlashGlobalNav")
Set frameBgTop = IE.document.getElementById("frameBgTop")
Set frameBgBottom = IE.document.getElementById("frameBgBottom")
Set frameContent = IE.document.getElementById("frameContent")
Set SearchInput = IE.document.getElementById("SearchInput")
Set Topbar = IE.document.getElementById("Topbar")
Set dealerL = IE.document.getElementById("DealerL")
Set InputBox1 = IE.document.getElementById("Inputbox")
Set tmap1 = IE.document.getElementById("tmpa1")
Set pages = IE.document.getElementById("dealerLocator.searchResults.page")
Set movFlashBottomNav = IE.document.getElementById("movFlashBottomNav")

End Sub