Atishoo;544679 Wrote:
I made a number of improvements to youir code
1) Only open the IE explorer once in the code and close it once. It is
quicker when you are looping athrough a lot of data
2) the Do event Loop : Combine the IE.readyState and IE.Busy into one
loop. I find at different website one becomes busy before the other but
it is not predicatable which becomes ready first.
3) I included a Dump routine so you can see the actual data. In sheet
you will see there are 10 tables (Do a find all on column A). You will
see the data you are looking for in cell D134. The webpage arrays start
at 0 (zero) so table 9 will be table.item(8). The rows and columns also
start at zero so you want to return row(5) : column(1).
Private Sub CommandButton1_Click()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
For Each c In Worksheets("Sheet1").Range("D6:N6").Cells
If c.Offset(0, 1).Value < "" Then
URL =
"http://www.driving-distances.com/distances-between-calculator.php"
IE.Navigate2 URL
Do While IE.readyState < 4 Or _
IE.busy = True
DoEvents
Loop
Set Form = IE.document.getElementsByTagname("Form")
Set inputform = Form.Item(0)
Set Postcodebox = inputform.Item(0)
Postcodebox.Value = c.Value
Set Postcodebox2 = inputform.Item(1)
Postcodebox2.Value = c.Offset(0, 1).Value
Set POSTCODEbutton = inputform.Item(2)
POSTCODEbutton.Click
Do While IE.readyState < 4 Or _
IE.busy = True
DoEvents
Loop
Call Dump(IE)
Set Table = IE.document.getElementsByTagname("table")
Set DistanceTable = Table.Item(8)
Set DistanceRow = DistanceTable.Rows(4)
c.Offset(1, 1) = Val(Trim(DistanceRow.Cells(1).innertext))
End If
Next
IE.Quit
End Sub
Sub Dump(IE)
With Sheets("sheet2")
Cells.ClearContents
RowCount = 1
For Each itm In IE.document.all
Range("A" & RowCount) = itm.Tagname
Range("B" & RowCount) = itm.classname
Range("C" & RowCount) = itm.ID
Range("D" & RowCount) = Left(itm.innertext, 1024)
RowCount = RowCount + 1
Next itm
End With
End Sub
--
joel
------------------------------------------------------------------------
joel's Profile:
http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=149872