Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,490
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

Code:
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

New Member
Joined
May 14, 2015
Messages
11
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

New Member
Joined
May 14, 2015
Messages
11
Re: Dynamic Qurey

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

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,490

ADVERTISEMENT

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

New Member
Joined
May 14, 2015
Messages
11
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.

Lcoation Name:
Sittingbourne Western Junction
CRS Code:
TIPLOC:
STNGWJN
Stanox No:
89028

Realtrains
Day:
28

Month:
09

Year:
2018

<tbody>
</tbody>

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

mikey141

New Member
Joined
May 14, 2015
Messages
11
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,528
Messages
5,548,571
Members
410,852
Latest member
WernerS
Top