CODEWITHSUNDEEP
htmlexcelvbasearchweb-scraping
user3617598
user3617598

Reputation: 1

Extracting multiple search results

I have created a VBA application that allows you to extract search results from the canada411.ca site. You simply insert values into to the values "Where" and "What" and "Title", "Location, and "Phone" will spit out. In my code What = "Name". Here is my code:

    Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Row = Range("Name").Row And _
 Target.Column = Range("Name").Column Then
  End If

 If Target.Row = Range("Where").Row And _
 Target.Column = Range("Where").Column Then

 'Set Variables What and Where from Canada411.ca to Values on Excel WorkSheet
  Dim IE As New InternetExplorer
  IE.Visible = True
  IE.navigate ("http://canada411.yellowpages.ca/search/si/1/") & _
  Range("Name").Value & "/" & Range("Where").Value

  Do
  DoEvents
  Loop Until IE.readyState = READYSTATE_COMPLETE
  Dim Doc As HTMLDocument
  Set Doc = IE.document
  'Extract from Canada411.ca Source element (first search result)
  Range("Title").Value = Trim(Doc.getElementsByTagName("h3")(0).innerText)
  Range("Phone").Value = Trim(Doc.getElementsByTagName("h4")(0).innerText)
  Range("Location").Value = Trim(Doc.getElementsByClassName("address")(0).innerText)
  IE.Quit

 'Extract for Second Search result

 'Third Search result etc.


  End If
 End Sub

My problem is that I don't know how to get the remaining results on the page, I can only get the first result on the first page. The source code for the subsequent search results are the same as the first, but I cannot seem to make it work. (Perhaps there is a shortcut after you have the code for the first one, to get the rest?) I am new to VBA and HTML and appreciate the help.

Upvotes: 0

Views: 262

Answers (2)

Joe Bleau
Joe Bleau

Reputation: 1

In your link change the « 1 » for a 2, 3, 4 ... These are the page numbers !

http://canada411.yellowpages.ca/search/si/1/
http://canada411.yellowpages.ca/search/si/2/
http://canada411.yellowpages.ca/search/si/3/
...

Upvotes: 0

S Meaden
S Meaden

Reputation: 8270

Well, you have two options.

1) Learn how to navigate the DOM using the Tools->References library 'Microsoft HTML Object Library' and extract that way.

2) It is possible to pull the web page into a Excel worksheet and then you only need to pull out data from each cell. Much easier but ties you to Excel. Use the Macro recorder and then use the GUI , on the Ribbon Data->From Web and follow the wizard.

Upvotes: 1

Related Questions