Changing Parameters by changing a cell

[ChrisK]

When importing data using a WEB QUERY in EXCEL 2003

http://www.metoffice.gov.uk/weather/europe/berlinforecast.html

I get the weather for berlin etc.

I then put a parameter in ["city"] so I get

http://www.metoffice.gov.uk/weather/europe/["city"]forecast.html and link
this to a cell

that I can change, and I can get the weather for any of the cities. Alls
Well!


This is in IE6. When I try to do this in IE8, the first part works, but
when set up with the parameter, the website cant be found.

However if I take a copy created in IE6, it will run on IE8, and can be found.

Have looked through advanced tabs etc and cant see the problem. Without
resorting to VBA, is there a solution?

 

[Don Guillett]

Try this. Just tested in ie8. You could even have a drop down list in A1
tied to a worksheet_change event to make it fully automatic.

Sub GetWeatherCitySAS()
With ActiveSheet.QueryTables(1)
.Connection = _
"URL;http://www.metoffice.gov.uk/weather/europe" & _
"/" & lcase(Range("a1")) & "forecast.html"
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub

 

[ChrisK]

Thanks for routine - keep getting 'Run time error 9'
What I was really looking for was how to use ["city"] in the URL in
Data-Import External Data - Edit Query.
I have created a list and it works in IE6 and will run in IE8, but I cant
create in IE8??
Want to demonstrate to a class how to use this parameter in a URL.

 

[Don Guillett]

Send me your file and I'll fix it

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Thanks for routine - keep getting 'Run time error 9'
What I was really looking for was how to use ["city"] in the URL in
Data-Import External Data - Edit Query.
I have created a list and it works in IE6 and will run in IE8, but I cant
create in IE8??
Want to demonstrate to a class how to use this parameter in a URL.
--
ChrisK

When importing data using a WEB QUERY in EXCEL 2003

http://www.metoffice.gov.uk/weather/europe/berlinforecast.html

I get the weather for berlin etc.

I then put a parameter in ["city"] so I get

http://www.metoffice.gov.uk/weather/europe/["city"]forecast.html and link
this to a cell

that I can change, and I can get the weather for any of the cities. Alls
Well!


This is in IE6. When I try to do this in IE8, the first part works, but
when set up with the parameter, the website cant be found.

However if I take a copy created in IE6, it will run on IE8, and can be
found.

Have looked through advanced tabs etc and cant see the problem. Without
resorting to VBA, is there a solution?