Excel Excel Web Query on URL: DirectGov PostCode not working

Joined
Jun 13, 2015
Messages
2
Reaction score
0
Hi, I am new to the forum. I have been working on a Excel Web Query dynamically in VBA to Find your local authority from within an Excel sheet from http://local.direct.gov.uk/LDGRedirect/index.jsp?mode=1.1

Have a worksheet holding one column of post codes data for say 1000 rows and really want to put an adjacent column of Authority names data next to them. or I could initially look them repeatedly up using a web query in excel on a single row with a named range on that cell.

I had initially browsed via google to locate a suitable unauthentic web site to take the field names from by inspecting their element names and tag names and looked at Google maps before deciding on the above site.

Having taken some tips from a You Tube video of
which does the same thing but for a US zipcode rather than a UK Postcode.

So here's is my code. The main problem being the above web site passes more than one argument and I only wanted to pass the 'text' for 'PostCode' and a return of 'Authority' as named cells in the worksheet.

Does anyone have a sample code to do a similar job as my code below to offer a returned 'Authority' when a given named cell holding 'PostCode' is changed for this site is not working.

Skelton Code that requires a lot of corrections so I have added some explanatory comments

Private Sub Worksheet_Change(ByVal Target As Range)
'added name range 'PostCode' in cell B1, and 'Authority' in B2 follwing YouTube https://www.youtube.com/watch?v=7sZRcaaAVbg
If Target.Row = Range("PostCode").Row And Target.Column = Range("PostCode").Column Then
Dim IE As New InternetExplorer
IE.Visible = True
'Web url INITIALLY shows http://local.direct.gov.uk/LDGRedirect/index.jsp?mode=1.1 and on entering a sample post code becomes 'http://local.direct.gov.uk/LDGRedir...=E125RW&mode=1.1?SerachTypeE125RW.1&formsub=t
'Tried adding ?parametername 'text'=E12 5RW on end of URL and minic this on following line

IE.navigate = "http://local.direct.gov.uk/LDGRedirect/index.jsp?text=?" & Range("PostCode").Value
'DEBUG gave arguements not declared as optional so tried to add following line for one or them
Sub OptionalArgs(strState As String, Optional Mode As Variant, Optional varCountry As Variant = "1.1")
'http://local.direct.gov.uk/LDGRedir...?searchtype=1&&mode=1.1.com.1&formsub=t&text=" & Range("PostCode").Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sDD As String
sDD = Trim(Doc.getElementsByTagName("li").innerText)
'Next line to test output works
MsxBox sDD
'The MsgBox to be eventually replaced with that below and if need a split
' IE.quit
' Dim aDD as variant
' aDD = Split(sDD,"")
' Range("Authority").value=aDD(1)
' Range("City").value=aDD(2), etc
End If
End Sub

Perhaps someone knows of a suitable Excel Web Query / VBL that can be repeated to return and post Authority name data adjacent to a column of postcode data.
 
Joined
Jun 13, 2015
Messages
2
Reaction score
0
Can anyone help resolve why the following code does not return the Local Authority name as line IE.Readystate does seem to complete (4) and returns web HTML initial tag values instead.

Private Sub Worksheet_Change(ByVal Target As Range)
'added name range 'PostCode' in sheet 1 cell B1, and 'Authority' in B2 as per YouTube
If Target.Row = Range("PostCode").Row And Target.Column = Range("PostCode").Column Then
Dim IE As New InternetExplorer
'IE.Visible = True
IE.navigate "http://local.direct.gov.uk/LDGRedirect/index.jsp?mode=1.1&text=" & Range("PostCode").Value
' Trying to resolve unreadiness state to retrieve the Authority name once passed PostCode to web HTML and it does not up date at present
Do
DoEvents ' Allows mutitasking and avoid curser hanging while operating system is busy
Loop Until IE.readyState = READYSTATE_COMPLETE And IE.Busy = False 'wait until loaded before next VBA line
Dim Doc As HTMLDocument
Set Doc = IE.document
Dim sDD As String
' inspected element of source HTML web code and found tag as "li" associated with Authority name to be returned
sDD = Trim(Doc.getElementsByTagName("li")(2).innerText)
'IE.Quit
Dim aDD As Variant
aDD = Split(sDD, " - ") 'Expected value 'Home page - London Borough of Newham' split out 'London Borough of Newham'
Range("Authority").Value = aDD(0)
'Range("Authority").Value = aDD(1) ' once working use split
End If
End Sub
 

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

Top