web query. data not coming. java scripts?

[edzsmith]

Hi, I'm trying to web query for some oyster ticket stop locations.

I am using this website with various post codes, for example EC1: http://ticketstoplocator.tfl.gov.uk/LocationLocator/default.asp?PostCode=EC1

(the website doesn't seem to work with chrome)

However when I run the web query in excel (for example see end of post) it does not draw location data. Instead the output into excel says "Location not found. Please try again."

I think this is because it gets very complicated with the java scripts in the page.

Any help would be appreciated as to how to do this.

Thanks.


below is an example script I've been using:

Sub Macro1()
'
' Macro1 Macro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://ticketstoplocator.tfl.gov.uk/LocationLocator/default.asp?PostCode=Ec1" _
, Destination:=Range("$A$1"))
.Name = "default.asp?PostCode=Ec1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[John_w]

If a web query doesn't work then try VBA IE automation - search for InternetExplorer and HTMLDocument for coding examples.