Hi Everyone, I would like to introduce myself as Karthick.
I need an help from your side, to "Pull the contact number's of companies
from a webpage (www.411.ca) and auto-populating in excel through macro".
I already pulled the data from www.yellopages.ca" for the list of company
names. I am attaching the output format & also the coding which i did for
"www.yellowpages.ca".
Grateful, if anyone can help to pull the contact numbers from the indicated
webpage.
*Coding to pull contact numbers of company names from "www.yellowpages.ca"
- *
Sub Test()
Dim ie As Object, ieDoc As Object
Dim i As Integer
Dim cName As String, tagcName As String, tagPhone As String
Dim istr As String
Dim cel As Range
Dim x As Variant
Set ie = CreateObject("InternetExplorer.Application")
For Each cel In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
If cel.Offset(, 3) = "" Then
cName = Replace(cel.Value, " ", "+")
Url = "http://www.yellowpages.ca/search/si/1/"; & cName & "/" & cel.Offset(,
2).Value
ie.navigate Url
Do Until (ie.readyState = 4 And Not ie.Busy)
DoEvents
Loop
Set ieDoc = ie.Document
istr = ieDoc.All(0).innerHTML
If htmlCheck(istr) Then
cel.Offset(, 3).Value = "Not Found"
GoTo Skip2:
End If
On Error GoTo Skip:
tagcName = ieDoc.getElementsByClassName("listing__name--link
jsListingName")(0).innerText
cel.Offset(, 5).Value = tagcName
Skip:
Resume NextStep:
NextStep:
On Error GoTo Skip1:
tagLink = ieDoc.getElementsByClassName("mlr__item__cta")(0).href
x = Split(tagLink, "?")
cel.Offset(, 4).Value = x(UBound(x))
Skip1:
Resume NextStep1:
NextStep1:
On Error GoTo Skip2:
tagPhone = ieDoc.getElementsByClassName("mlr__submenu__item")(0).innerText
cel.Offset(, 3).Value = tagPhone
End If
Skip2:
Next cel
Set ie = Nothing
'MsgBox "Process Complete"
End Sub
Public Function htmlCheck(pStr As String) As Boolean
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
With regex
.Pattern = "\b(We didn’t find any business listings matching)\b"
End With
htmlCheck = regex.Test(pStr)
End Function
Thanks
Kaarthick
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
CompanyContact_Rev-10.xls
Description: MS-Excel spreadsheet
