Excel Web Query

[arvarr]

Hi
Hoping someone can help me with Excel Web Query.

I am trying to import data from the below website:

http://www.timeanddate.com/worldclock/

I can import the whole page but hoping to be able to utilise the Search For City function on the website.

Rather than importing all the cities, I would like to import only the city that i specified. Tried to edit the query but without much success.

Thanks.
Arvarr

 

[SkipVought]

hi,

Importing all the cities: is that a problem?

I'd like to be a six footer, too, oh and about 40 years younger, if that is possible!

Web queries just seem to return a page or table, if you know which one. No other criteria.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
Thanks for your reply.

So, its not possible to submit a query just for the desired city.

Thanks.

 

[SkipVought]

It's just a page of data, not millions of rows. What is the issue?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
There are some cities that are not avail on the 1 page of data.
Hence, wanting to use the search function.
Thanks.

 

[SkipVought]

1. IMPORT into Excel

2. SEARCH in Excel

???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arvarr]

Hi Skip
As per my above reply, the list that I am able to import from

http://www.timeanddate.com/worldclock/

only shows a limited list.
E.g. I try to find Aberdeen from the list (either imported into Excel or from the above webpage) and it is not available/visible unless I use the search for city function on the webpage.
Thanks.

 

[Gruuuu]

Do you know which cities will be searched for? If so search them yourself and build a URL list. It appears the website has a specific URL for each city.

Otherwise, there's no EASY way to achieve what you're looking for. Which is not to say it cannot be done.

 

[arvarr]

Hi Gruuuu
Thanks for the the recommendation.
Yes, it looks like each city is numbered hence the sticky situation.
Thanks for your help anyway.

 

[SkipVought]

To generate this list, I would access the cities in a loop , using

http://www.timeanddate.com/worldclock/city.html?n=

[highlight]786[/highlight] and write the result to a table in another sheet.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[oharab]

What are you trying to get to?
Are you after a list of all available cities, or a search screen in access?
The fist option Skip has sorted, for the second you'll have to do some screen scraping, probably using the HttpRequest object.
Either way, I think you will be breaching their terms and conditions:

You may not use extraction tools or tools to download parts of this website, or the entire website.

B.

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.

 

[oharab]

Looking further into their site, they have a web API which you can use to query their data, but there is a charge for it.

http://www.timeanddate.com/services/api/time-api.html#

hth

Ben

----------------------------------------------
Ben O'Hara

We could be confused in exactly the same way, but confusion might be like Nulls, and not comparable.