snwskier2
10-27-2009, 07:03 PM
Here is what I am trying to accomplish along with the code I've written thus far. I have input to website via xls, but am having a tough time with the web extraction.
An Excel database 'sheet2' consisting of properties and property contacts. Each row contains a property and related contacts.
1. Extract their "lastname" "firstname" "city" / "zip" and "state" from 'sheet2 book1.xls', find their contact information via Anywho.com or WhitePages.com or any other directory,
2. Paste the 1 or Many results (i.e. name, address, and phone number) along with the corresponding Property "PIN", in 'sheet2 book1.xls'.
2.5. There may be more than one related contact for any one property, all property related contacts are on the same row.
3. Run the Loop until it reaches the last property PIN or Contact.
Example (xls sheet1)
PIN+lastname+firstname+city+state+zip+lastname2+firstname2+city2
1212123123, Doe, John, Chicago, IL, 60601, Smith, James, Plainfield
///////////////////////////////////////////////////////////////////////////////
Sub AnyWhoSearch()
'This project includes references to "Microsoft Internet Controls, Microsoft HTML Object Library"
'Variable declarations
Dim myIE As New InternetExplorer
Dim myURL As String
Dim myDoc As HTMLDocument
Dim strSearch As String
Dim newHour As Variant
Dim newMinute As Variant
Dim newSecond As Variant
Dim waitTime As Variant
Dim cn As Range
Dim cf As Range
Dim cc As Range
Dim cs As Range
Dim cz As Range
'On Error GoTo errHandler
'Set starting range (first cell of data)
Set cn = Sheets("Sheet2").Range("v2")
Set cf = Sheets("Sheet2").Range("t2")
Set cc = Sheets("Sheet2").Range("z2")
Set cs = Sheets("Sheet2").Range("aa2")
Set cz = Sheets("Sheet2").Range("ab2")
'Set starting URL and search string
myURL = "w.anywho.com/wp.html"
'loop through list of data
Do While cn.Value <> vbNullString
'Make IE navigate to the URL and make browser visible
myIE.Navigate myURL
myIE.Visible = True
'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
'Set IE document into object
Set myDoc = myIE.document
'Enter search string on form
myDoc.forms(0).qn.Value = cn.Value
myDoc.forms(0).qf.Value = cf.Value
myDoc.forms(0).qc.Value = cc.Value
myDoc.forms(0).qs.Value = cs.Value
myDoc.forms(0).qz.Value = cz.Value
'Submit form
myDoc.forms(0).submit
'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 15
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Set cn = cn.Offset(1, 0)
Set cf = cf.Offset(1, 0)
Set cc = cc.Offset(1, 0)
Set cs = cs.Offset(1, 0)
Set cz = cz.Offset(1, 0)
Loop
errHandler:
myIE.Quit: Set myIE = Nothing
End Sub
///////////////////////////////////////////////////////////////////////////////////
Also, any ideas on how to retrieve without opening up IE?
You help is greatly appreciated.
Snwskier2
An Excel database 'sheet2' consisting of properties and property contacts. Each row contains a property and related contacts.
1. Extract their "lastname" "firstname" "city" / "zip" and "state" from 'sheet2 book1.xls', find their contact information via Anywho.com or WhitePages.com or any other directory,
2. Paste the 1 or Many results (i.e. name, address, and phone number) along with the corresponding Property "PIN", in 'sheet2 book1.xls'.
2.5. There may be more than one related contact for any one property, all property related contacts are on the same row.
3. Run the Loop until it reaches the last property PIN or Contact.
Example (xls sheet1)
PIN+lastname+firstname+city+state+zip+lastname2+firstname2+city2
1212123123, Doe, John, Chicago, IL, 60601, Smith, James, Plainfield
///////////////////////////////////////////////////////////////////////////////
Sub AnyWhoSearch()
'This project includes references to "Microsoft Internet Controls, Microsoft HTML Object Library"
'Variable declarations
Dim myIE As New InternetExplorer
Dim myURL As String
Dim myDoc As HTMLDocument
Dim strSearch As String
Dim newHour As Variant
Dim newMinute As Variant
Dim newSecond As Variant
Dim waitTime As Variant
Dim cn As Range
Dim cf As Range
Dim cc As Range
Dim cs As Range
Dim cz As Range
'On Error GoTo errHandler
'Set starting range (first cell of data)
Set cn = Sheets("Sheet2").Range("v2")
Set cf = Sheets("Sheet2").Range("t2")
Set cc = Sheets("Sheet2").Range("z2")
Set cs = Sheets("Sheet2").Range("aa2")
Set cz = Sheets("Sheet2").Range("ab2")
'Set starting URL and search string
myURL = "w.anywho.com/wp.html"
'loop through list of data
Do While cn.Value <> vbNullString
'Make IE navigate to the URL and make browser visible
myIE.Navigate myURL
myIE.Visible = True
'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
'Set IE document into object
Set myDoc = myIE.document
'Enter search string on form
myDoc.forms(0).qn.Value = cn.Value
myDoc.forms(0).qf.Value = cf.Value
myDoc.forms(0).qc.Value = cc.Value
myDoc.forms(0).qs.Value = cs.Value
myDoc.forms(0).qz.Value = cz.Value
'Submit form
myDoc.forms(0).submit
'Wait for the page to load
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 15
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Set cn = cn.Offset(1, 0)
Set cf = cf.Offset(1, 0)
Set cc = cc.Offset(1, 0)
Set cs = cs.Offset(1, 0)
Set cz = cz.Offset(1, 0)
Loop
errHandler:
myIE.Quit: Set myIE = Nothing
End Sub
///////////////////////////////////////////////////////////////////////////////////
Also, any ideas on how to retrieve without opening up IE?
You help is greatly appreciated.
Snwskier2