Debug help

[dantheram]

Hi all,

i cannot debug this code, i suspect it's not pushing the postcodes into the website but it's giving me precious little feedback as to whats wrong - any ideas?

Sub Postcode_Distances()

Dim URL As String
Dim count As Integer
Dim waitCounter As Integer
Dim fromCheck As String
Dim toCheck As String
Dim explorerCounter As Integer
Dim resetExplorer As Integer
Dim waitTime As Integer ' seconds


count = 1
distance = 0
prevDistance = 0
prevdistance_direct = 0
waitTime = 2 'seconds
explorerCounter = 0
resetExplorer = 500 'restart explorer after this number of runs
URL = "http://www.freemaptools.com/distance-between-uk-postcodes.htm"


'get to webpage
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate2 URL
Do While IE.readyState <> 4 Or IE.busy = True
    DoEvents
Loop
        
' Read website information for inputting postcodes
Set form = IE.Document.getElementsByTagName("Form")
Set inputform = form.Item(0)
Set Postcodebox = inputform.Item(0)
Set Postcodebox2 = inputform.Item(1)
Set POSTCODEbutton = inputform.Item(2)
        
Worksheets("Postcodes").Range("A1").Select
Calculate


Do While Range("Postcode_From_Check").Offset(count, 0) <> ""


    fromCheck = Range("Postcode_From_Check").Offset(count, 0).Value
    toCheck = Range("Postcode_To_Check").Offset(count, 0).Value
    manualCheck = Range("Manual_Postcode_Check").Offset(count, 0).Value
    
    If fromCheck = "Invalid" Or toCheck = "Invalid" Or manualCheck = "Invalid" Then
        Range("Distance").Offset(count, 0) = "xxxx"
        Range("Distance_Direct").Offset(count, 0) = "xxxx"
    ElseIf Range("Distance").Offset(count, 0) = "" Then
        ' Read 'from' postcode - if valid use given postcode, otherwise use corrected version
        Range("Distance").Offset(count, 0).Select
        If fromCheck = "Valid" Then
            StartLocation = Range("Postcode_From").Offset(count, 0).Value
        Else
            StartLocation = fromCheck
        End If
        
        ' Read 'to' postcode - if valid use given postcode, otherwise use corrected version
        If toCheck = "Valid" Then
            EndLocation = Range("Postcode_To").Offset(count, 0).Value
        Else
            EndLocation = toCheck
        End If
        
        ' Input postcodes and 'click' button
        Postcodebox.Value = StartLocation
        Postcodebox2.Value = EndLocation
        POSTCODEbutton.Click


        ' Wait until website is ready (this step may not be necessary for freemaptools.com)
        DoEvents
        Do While IE.readyState <> 4 Or IE.busy = True
            DoEvents
        Loop
        
        'Pause for a second
        Application.Wait Time + TimeSerial(0, 0, waitTime)
        
        waitCounter = 0
        
        ' Read website information reading off distance information
        Set Table = IE.Document.getElementsByTagName("Table")
        Set DistanceTable = Table.Item(0)
        Set DistanceRow = DistanceTable.Rows.Item(5)
        Set DistanceElement = DistanceRow.Cells.Item(0).Children(1)
        Set DistanceRow_Direct = DistanceTable.Rows.Item(4)
        Set DistanceElement_Direct = DistanceRow_Direct.Cells.Item(0).Children(1)
        
        ' Take distance from website
        distance = Val(Trim(DistanceElement.Value))
        distance_direct = Val(Trim(DistanceElement_Direct.Value))
        
        Do While prevDistance = distance Or prevdistance_direct = distance_direct
            ' Read website information reading off distance information
            Set Table = IE.Document.getElementsByTagName("Table")
            Set DistanceTable = Table.Item(0)
            Set DistanceRow = DistanceTable.Rows.Item(5)
            Set DistanceElement = DistanceRow.Cells.Item(0).Children(1)
            Set DistanceRow_Direct = DistanceTable.Rows.Item(4)
            Set DistanceElement_Direct = DistanceRow_Direct.Cells.Item(0).Children(1)
            
            ' Take distance from website
            distance = Val(Trim(DistanceElement.Value))
            distance_direct = Val(Trim(DistanceElement_Direct.Value))
            waitCounter = waitCounter + 1
            
            ' If takes too long then enter message
            If waitCounter > 5 * 10 ^ 3 Then
                'errorCheck = Left(DistanceTable.Rows.Item(2).innertext, 5)
                Range("Distance").Offset(count, 0) = "as above?"
                Range("Distance_Direct").Offset(count, 0) = "as above?"
                Exit Do
            End If
        Loop
        prevDistance = distance
        prevdistance_direct = distance_direct
        Range("Distance").Offset(count, 0).Select
        If Selection <> "as above?" Then
            Selection = distance
            Range("Distance_Direct").Offset(count, 0) = distance_direct
        End If
        
        'Reset Explorer
        explorerCounter = explorerCounter + 1
        If explorerCounter >= resetExplorer Then
            IE.Quit
            Set IE = CreateObject("InternetExplorer.Application")
            IE.Visible = True


            'get to webpage
            IE.Navigate2 URL
            Do While IE.readyState <> 4 Or IE.busy = True
                DoEvents
            Loop


            ' Read website information for inputting postcodes
            Set form = IE.Document.getElementsByTagName("Form")
            Set inputform = form.Item(0)
            Set Postcodebox = inputform.Item(0)
            Set Postcodebox2 = inputform.Item(1)
            Set POSTCODEbutton = inputform.Item(2)
            explorerCounter = 0
            prevDistance = 0
            prevdistance_direct = 0
        End If
        
    End If
    
    count = count + 1
Loop


IE.Quit


End Sub

 

[BarryL]

what line is it stopping on? whats the error you're getting?

 

[dantheram]

what line is it stopping on? whats the error you're getting?

this is the problem - there isnt one

It's running through to completion but returning 0 output - this is why i think its not filling in the form correctly / or at all

 

[BarryL]

when you step through it with F8, does it enter in the values into the boxes and submit the form at all?

 

[dantheram]

when you step through it with F8, does it enter in the values into the boxes and submit the form at all?

nope - it runs till it reaches its self-defined timeout and return "as above"

 

[Norie]

As you step through the code have you checked what's actually happening in IE?

Are postcodes being entered and submitted?

If they are, is anything being returned?

 

[dantheram]

As you step through the code have you checked what's actually happening in IE?

Are postcodes being entered and submitted?

If they are, is anything being returned?

nothing happens in IE - it loads, as expected, but nothing else. Interestingly, there is some interaction, as the map embeded on the web page goes from not showing major UK cities / seas to showing both - this occurs when the VBA steps over 'POSTCODEbutton.Click'

 

[Norie]

What happens if instead of clicking the button to submit the form you submit it like this.

inputform .submit

 

[dantheram]

What happens if instead of clicking the button to submit the form you submit it like this.

inputform .submit

same thing - returns "as above" with no interaction with web page

 

[BarryL]

i think im having problems with objects not being defined .

I stripped it back and did a small test on the below and it worked, is the below what you expect the webpage to do?

reference for Microsoft Internet Controls needs to be added first.

Sub test()
'tools > references > Microsoft Interent Controls
Dim IE As New InternetExplorer
Dim inpt As Object, ieObj As Object, ieBtn As Object, btn As Object




IE.Visible = True
IE.Navigate "http://www.freemaptools.com/distance-between-uk-postcodes.htm"
Do While IE.ReadyState <> 4 Or IE.Busy = True
    DoEvents
Loop


Set inpt = IE.Document.getelementsbytagname("Input")


StartLocation = "Cr3 0BF"
EndLocation = "CR3 0EA"


For Each ieObj In inpt
If ieObj.Name = "pointa" Then ieObj.Value = StartLocation
If ieObj.Name = "pointb" Then ieObj.Value = EndLocation
Next
Set btn = IE.Document.getelementsbyclassname("fmtbutton")


For Each ieBtn In btn
If ieBtn.innertext = " Show " Then
ieBtn.Click
Exit For
End If
Next
'IE.Quit
End Sub