Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel values to Website, HTML extraction back to Excel. PLEASEHelp!!!
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+fi rstname2+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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get back values in the excel bulidin dialogs ? | Excel Programming | |||
Passing Excel Values back to a SQL Table | Excel Programming | |||
Copying HTML Data From Website problem | Excel Discussion (Misc queries) | |||
saving website html as string | Excel Programming | |||
Excel to HTML link - and back? | Links and Linking in Excel |