Dynamic Query

[mikey141]

I’ve been trying to find a way to change location and date from import web page and wondered if somebody could help please?

http://www.realtimetrains.co.uk/search/advanced/RAM/2018/09/26/0000-2359?stp=WVS&show=all&order=wtt

The two areas are highlighted in bold and “RAM” I was going to use a lookup field. The change of date

I am using Excel 2016

 

[mrshl9898]

Re: Dynamic Qurey

Depends what you're trying to accomplish, something like this i'd set up some cells to enter the values I need in, and use that to build the URL I needed for my query.

Something like this

Sub Macro1()


Dim locstr As String
Dim yearstr As String
Dim daystr As String
Dim monthstr As String


locstr = Sheets("Sheet2").Range("A1").Value
yearstr = Sheets("Sheet2").Range("A2").Value
daystr = Sheets("Sheet2").Range("A3").Value
monthstr = Sheets("Sheet2").Range("A4").Value


With ActiveSheet.QueryTables(1)
    .Connection = "URL;http://www.realtimetrains.co.uk/search/advanced/" & locstr & "/" & yearstr & "/" & daystr & "/" & monthstr & "/0000-2359?stp=WVS&show=all&order=wtt"
    .Refresh
End With


End Sub

 

[mikey141]

Re: Dynamic Qurey

Thanks I will give this ago as it’s what I was looking for to input value in the sheet and return a query from the site.

 

[mikey141]

Re: Dynamic Qurey

I've tried the code but got stuck with A Debug error "With ActiveSheet.QueryTables(1)"

 

[mrshl9898]

Re: Dynamic Qurey

It will need to be changed to your current query table.

In this case I set up a query table with your current URL, then used that code to change it as the user updates the values.

 

[mikey141]

Re: Dynamic Qurey

Hiya,
I must be something really wrong here to get this working correctly using the code below
and have included in yellow the fields that need to change the URL address before it looks up query and find results.

[TABLE="width: 321"]
<tbody>[TR]
[TD="width: 114, bgcolor: transparent"]Lcoation Name:
[/TD]
[TD="width: 313, bgcolor: transparent"]Sittingbourne Western Junction
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]CRS Code:
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]TIPLOC:
[/TD]
[TD="bgcolor: yellow"]STNGWJN
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Stanox No:
[/TD]
[TD="bgcolor: transparent"]89028

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 2"]Realtrains
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Day:
[/TD]
[TD="bgcolor: yellow"]28

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Month:
[/TD]
[TD="bgcolor: yellow"]09

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Year:
[/TD]
[TD="bgcolor: yellow"]2018
[/TD]
[/TR]
</tbody>[/TABLE]

It needs to get data from: http://www.realtimetrains.co.uk/sea...18/09/28/0000-2359?stp=WVS&show=all&order=wtt

 

[mikey141]

Re: Dynamic Qurey

Evening,
I must be doing something really wrong with the code as itsnot working for me to change the fields of Location, Date before downloading aQuery from website. I have shared the sheet in Google Drive if somebody canhelp please?

http://www.realtimetrains.co.uk/search/advanced/STNGWJN/2018/09/28/0000-2359?stp=WVS&show=all&order=wtt

Mainsheet highlighted in YELLOW connected with above red

https://drive.google.com/file/d/16ZN55QZtXNPoxBUOGO60rt_F2TbcSKMG/view?usp=sharing