Getting UK postcode using address (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 12:17
Joined
Sep 21, 2011
Messages
6,317
Hi Vlad
Code looks good but I get an error with the line Dim MyDict as Dictionary

I've tried various workarounds such as
Dim MyDict
...
Set MyDict=CreateObject("Scripting.Dictionary")

but then get error 424 on the GetElementbyID line

Late here and about to sign off so no time to investigate properly
I too get the 424 error on that line. :(
At first I thought it was because my initial string was not being found? I tried it manually at the site.
So I entered the full address and it presented my street and postcode and still got the error.?

Edit:
Got it working with
Code:
 ie.navigate "192.com/places/"
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 12:17
Joined
Sep 21, 2011
Messages
6,317
isladogs, add Microsoft Scripting Runtime in references.
That is something I have never been able to work out.?

What reference is required for a particular function.? I happened to have that reference in my test db for some reason.
 

isladogs

CID
Local time
Today, 12:17
Joined
Jan 14, 2017
Messages
13,602
isladogs, add Microsoft Scripting Runtime in references.
Thanks but I had already done that.
After tweaking the URL, I can confirm it now works for me. :. sometimes ..though error 424 still occurs at other times.
Unfortunately it is very slow. Typically about 7 seconds per search.
Also when I search for a specific house in a street with multiple postcodes, it returns all values rather than just the correct one for that house
I'm going to have a further look at it this evening UK time to see if I can fix that
 

Владимир

New member
Local time
Today, 14:17
Joined
May 30, 2020
Messages
12
Yes, this method is slower than the method using the "MSXML2.XMLHTTP" object.
Time is wasted opening Internet Explorer and navigating (entering an address, pressing a button ...)


I rewrote the code of my function, however, sometimes I get an error on the line: "tempStr = Trim (ie.Document.getElementById (" ont-result-items "). InnerText)" - Object requery

After I go to the vba code page and click on the debug button
function operation continues. It seems that the page has not yet loaded, so the object "ont-result-items" was not found.

Code:
Option Compare Database
Option Explicit

#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) ' For 64 Bit Systems
    Public Declare PtrSafe Function DeleteUrlCacheEntry Lib "wininet" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)            ' For 32 Bit Systems
    Public Declare Function DeleteUrlCacheEntry Lib "wininet" Alias "DeleteUrlCacheEntryA" (ByVal lpszUrlName As String) As Long
#End If

Public Function seachPostcode(ByVal txtAddress As String) As String
    Dim ie As Object
    Dim tempStr As String
    Dim myArray() As String
    Dim i As Integer
  
    Dim myDict
    Dim varItem As Variant
  
On Error Resume Next
  
    seachPostcode = ""
  
    Set ie = CreateObject("Internetexplorer.application")
  
    ' invisible Internet Explorer
    ie.Visible = False

    ie.navigate "https://www.192.com/places/"

    While ie.busy
        DoEvents
    Wend

    ie.Document.getElementById("where_location").Value = txtAddress

    ' press button
    ie.Document.Forms(0).submit

    While ie.busy
        DoEvents
    Wend

    Sleep (50)
  
    tempStr = Trim(ie.Document.getElementById("ont-result-items").innerText)

    ie.Quit
    Set ie = Nothing

    myArray = Split(tempStr, "Postcode details")
  
    If UBound(myArray) > 0 Then
  
        ' create an object and assign a link to it a variable
        Set myDict = CreateObject("Scripting.Dictionary")
  
        For i = LBound(myArray) To UBound(myArray) - 1

            While myArray(i) Like (vbNewLine & "*")
                myArray(i) = Trim(Right(myArray(i), Len(myArray(i)) - 2))
            Wend
            
            While myArray(i) Like ("*" & vbNewLine)
                myArray(i) = Trim(Left(myArray(i), Len(myArray(i)) - 2))
            Wend
              
            tempStr = Split(myArray(i), vbNewLine)(0)
          
            If UBound(Split(tempStr, ",")) > 0 Then
                tempStr = Split(tempStr, ",")(1)
            End If
          
            tempStr = Trim(tempStr)

            myDict.Item(tempStr) = tempStr
          
        Next
      
        tempStr = ""
        For Each varItem In myDict.Items '
            tempStr = tempStr & varItem & vbNewLine
        Next
      
        seachPostcode = tempStr
      
        Set myDict = Nothing
  
    End If
  
    Call DeleteUrlCacheEntry("https://www.192.com/places/")

End Function
 
Last edited:

isladogs

CID
Local time
Today, 12:17
Joined
Jan 14, 2017
Messages
13,602
Hi Vladimir
Sorry to say I can no longer get any output using either your new code or the previous version. The message box is empty
I've tried various approaches including adding proper error handling to fix error 424 and debug.print of tempStr (its empty)
I've tested with the exact output from the 192.com/places site. Still nothing
I've noticed that IE does not quit & I end up with multiple instance of IE open

If I have time, I will try adapting my code in the Postcode Address Lookup example app which uses MSXML2.XMLHTTP60 and see if I can achieve the same result as you want. Hopefully it also be faster
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom