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