• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a little help to complete my vba code

Status
Not open for further replies.

shahin

Active Member
Dear all, I have made a parser to scrape yellowpage. It is doing fine so far with five columns filled in [1. Name 2. Street 3. Locality 4. Region 5. Postal Code]. However, the problem is that I can't extract the phone number which should be in 6th column. That is why I need help to get it done. If anybody out there gives me a little push on this, It would be a great help for me. Thanks in advance.

Code:
Option Explicit
Sub ResTest()
Const URL = "http://www.yellowpages.com/search?search_terms=Coffee%20Shops&geo_location_terms=San%20Francisco%2C%20CA&page=2"
Const ext = "http://www.yellowpages.com"

Dim http As New MSXML2.XMLHTTP60, html As New HTMLDocument
Dim topics As Object, topic As Object, link As Object
Dim newlink As String
Dim P As Long, N As Long, L As Long, str As Variant

L = 2

http.Open "GET", URL, False
http.send
html.body.innerHTML = http.responseText

Set topics = html.getElementsByClassName("business-name")

For Each topic In topics
newlink = ext & Replace(topic.href, "about:", "")

With http
 .Open "GET", newlink, False
 .send
 str = Split(.responseText, "<h1 itemprop=""name"">")
 N = UBound(str)
    For P = 1 To N
        Cells(L, 1) = Split(str(P), "<")(0)
        Cells(L, 2) = Split(Split(str(P), "itemprop=""streetAddress"">")(1), "<")(0)
        Cells(L, 3) = Split(Split(str(P), "itemprop=""addressLocality"">")(1), "<")(0)
        Cells(L, 4) = Split(Split(str(P), "itemprop=""addressRegion"">")(1), "<")(0)
        Cells(L, 5) = Split(Split(str(P), "itemprop=""postalCode"">")(1), "<")(0)
        '''Cells(L, 6) = Phone Number>> this is where I got stuck
        L = L + 1
    Next P
End With
Next topic
End Sub
 
HTML element for Phone Number is lying in this portion:

Code:
<p itemprop="telephone" class="phone">(415) 474-1871</p>
 
Sorry brother, this is a bit misleading. I tried with this when I wrote my code. Anyways, thanks for your reply.
 
Hi !​
<p itemprop="telephone" class="phone">(415) 474-1871</p>
Code:
Sub Test()
    Const H = "<p itemprop=""telephone"" class=""phone"">(415) 474-1871</p>"
    MsgBox Split(Split(H, "class=""phone"">")(1), "<")(0)
End Sub
Like in your previous thread where you grabbed this code
delimiter must be the same as within webpage code !

If you do not understand how Split works,
use InStr VBA function to locate text part within a global text
and Mid to extract text part from a position … (at beginner level)

I don't understand your two requests as only one is necessary.

▬▬▬▬▬▬▬▬▬ Mod edit : thread closed as wild cross posting !

Read this !

► From New Users - Please Read :

Please Don't :
  • Cross-Posting. Generally, it is considered poor practice to cross post.That is to post the same question on several forums in the hope of getting a response quicker.
  • If you do cross-post, please put that in your post.
  • Also if you have cross-posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.
 
Status
Not open for further replies.
Back
Top