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
 
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. ;)
 
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

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