Mileage Calculator by post code / zip code

A

Atishoo

Hi
I am adapting the following sub to work with a more accurate web site (one
that calculates distances based on the full post code)
I am getting object variable / block variable not set on the last line:
c.Offset(1, 1) = Val(Trim(DistanceRow.Cells(2).innertext)).
Am i referencing the IE table incorectly?
Any ideas apreciated.

Private Sub CommandButton1_Click()

For Each c In Worksheets("Sheet1").Range("D6:N6").Cells
If c.Offset(0, 1).Value <> "" Then

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

URL = "http://www.driving-distances.com/distances-between-calculator.php"
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop
Do While 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.busy = True
Loop

Set Table = IE.document.getElementsByTagname("table")
Set DistanceTable = Table.Item(1)

Set DistanceRow = DistanceTable.Rows(6)

c.Offset(1, 1) = Val(Trim(DistanceRow.Cells(2).innertext))

IE.Quit
End If
Next

End Sub
 
J

joel

I made a number of improvements to youir code

1) Only open the IE explorer once in the code and close it once. It i
quicker when you are looping athrough a lot of data

2) the Do event Loop : Combine the IE.readyState and IE.Busy into on
loop. I find at different website one becomes busy before the other bu
it is not predicatable which becomes ready first.

3) I included a Dump routine so you can see the actual data. In shee
you will see there are 10 tables (Do a find all on column A). You wil
see the data you are looking for in cell D134. The webpage arrays star
at 0 (zero) so table 9 will be table.item(8). The rows and columns als
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 Su
 
M

Mike H

Hi,

I can't get a reference to the table either but can cheat. This will
populate the 4 cells below the postcode with the 4 distances the web page
calculates. I hope someone comes up with a less messy solution for you

Private Sub CommandButton1_Click()
For Each c In Worksheets("Sheet1").Range("D6:N6").Cells
If c.Offset(0, 1).Value <> "" Then
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
URL = "http://www.driving-distances.com/distances-between-calculator.php"
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop
Do While 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.busy = True
Loop
RowCount = 1
For Each itm In IE.document.all
If Val(itm.innertext) <> 0 Then
c.Offset(RowCount, 1) = itm.innertext
RowCount = RowCount + 1
End If
Next itm
IE.Quit
End If
Next
End Sub

Mike
 
M

Mike H

Glad i could help but you must be able to refer to the table instead of
everyting on the web page, I just don't know how to do it.

Mike
 
A

Atishoo

Thanks Mike, been struglin with that for ages!

Mike H said:
Hi,

I can't get a reference to the table either but can cheat. This will
populate the 4 cells below the postcode with the 4 distances the web page
calculates. I hope someone comes up with a less messy solution for you

Private Sub CommandButton1_Click()
For Each c In Worksheets("Sheet1").Range("D6:N6").Cells
If c.Offset(0, 1).Value <> "" Then
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
URL = "http://www.driving-distances.com/distances-between-calculator.php"
IE.Navigate2 URL
Do While IE.readyState <> 4
DoEvents
Loop
Do While 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.busy = True
Loop
RowCount = 1
For Each itm In IE.document.all
If Val(itm.innertext) <> 0 Then
c.Offset(RowCount, 1) = itm.innertext
RowCount = RowCount + 1
End If
Next itm
IE.Quit
End If
Next
End Sub

Mike
Click to expand...
 
W

Walter Briscoe

In message <[email protected]> of Sun, 1 Nov 2009 12:09:36 in
microsoft.public.excel.programming said:
I made a number of improvements to youir code
Click to expand...

If I read enough of joel's contributions, I should get as much knowledge
of Internet Explorer's Document Object Model as I want to automate
website interaction.

[snip]
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()
[snip]

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
Click to expand...

I think joel forgot some periods!
The code splats Sheet1 with Microsoft Excel 2002 (10.2614.2625). (I
don't have immediate access to 2003 to test compatibility.) I found the
following code worked better by writing to sheet2:

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

I am in want of a generic DOM dump which will do a full job.
I reckon such a utility should simplify page analysis.
I await delivery of a Mozilla book to reduce my ignorance. ;)
 
J

joel

PHP code:
--------------------

The single period works on excel 2003, not sure why you ned two periods in 2007.

The Dump Subroutine is using sheet 2 and will give an error if you don't have sheet 2 in the workkbook.

IE versons 7 and 8 has a good developers tool by pressing F12.


Your table reference isn't working because the index to arrays start at 0 not one. The table is two columns with each column being cells(0), and cells(1). You have cells(2) which produces the error.
 
P

Peter T

Hi Joel,

Looks like a typo or error in posting, as Walter mentioned -

Sub Dump(IE)

With Sheets("sheet2")
Cells.ClearContents ' << needs a dot to qualify to With
RowCount = 1
For Each itm In IE.document.all
Range("A" & RowCount) = itm.Tagname ' << needs a dot to qualify to With

..Cells.ClearContents
RowCount = 1
..Range("A" & RowCount) = itm.Tagname
etc

might be an idea to declare the variables

Regards,
Peter T
 
P

Peter T

Hmm, seems like NNTP posts are not being propagated to Microsoft's
Communities, aka "WebNews", again!

IOW it's unlikely Atishoo and Mike H will see NNTP replies by Joel, Walter
and myself. Maybe these posts will get through in the next few days.

Regards,
Peter T
 
M

Mike H

Hi,

I can see Joel's post and note the much improved version on that I used. As
I said in my response to the OP I knew my code wasn't good and am pleased
Joel has shown us how to do it properly.

Mike
 
J

joel

You can convert any IE application to an XML application. There i
really no difference except the IE has visual data that takes longer t
download. they both use exactly the same URL
 
J

JP

Web APIs are optimized to return results, without the overhead of the
visual elements that HTML produces. If you can find a site that offers
one, the advantages are greater than you suggest. The code is much
shorter, and executes faster. And in most cases, there is a specific
interface provided at a custom URL, whereas IE automation simply
mimicks what a user would do when visiting the "public" page provided
by a web service.

--JP
 
A

Atishoo

Thanks Joel
And thanks for the links have applied the same principles to spread sheets
using web pages providing costs benefits analysis, in place of web queeries
with much better effect.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Request for Postal Codes 5
sub for calculating distance via post code / variable range. 7
sub to get post code to post code mileage from web site 3

Top