Insert Zip codes into weather code

[L. Howard Kittle]

Helo Excel users and experts,

Excel 2002 SP3

The code below is supposed to return a short weather update from the area of
the zip code. It does not work, and no error prompt is given. If I remove
the two words "Zip" and type in 97401 in their place I will get the weather
update just fine..

I have a list of zip codes in a drop down in D1 of the worksheet. As you
can see I'm trying to Dim that D1 value as Zip to insert a new selected zip
code for different regional updates.

I've tried dimming as Long, and as String and used "Zip", Zip, &Zip& to no
avail.

How do I change the zip code in the weather code??

(By the way, you will see a double line break, _ _ in the "With
ActiveSheet.QueryTables..." line. I put it there for this post only as that
line of code does not accept a line break in the editor past the one already
there after "(Connection:= _".)

Sub SomeWx()
Dim Zip As Long
Zip = Range("D1").Value

Range("A1:B5").ClearContents

Range("A1").Value = "Eugene"
Range("C1") = Now()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.weather.com/weather/local/Zip? _ _
lswe=Zip&lwsa=WeatherLocalUndeclared&from=searchbox_localwx""" _
, Destination:=Range("$A$2"))
.PreserveFormatting = True
.WebTables = "15"
.Refresh BackgroundQuery:=False
End With
End Sub

Perhaps I may have to use a select case set up with the various zip codes
hardcoded for each region.

Thanks for any help or suggestions.
Regards,
Howard

 

[Rick Rothstein]

Anything inside quote marks is considered text, you the appearance of Zip
inside the URL string is just the letters Z, i, and p and has nothing to do
with the variable you named Zip. Think about it for a moment... if the code
actually worked the way you wrote it, how would you ever be able to use the
word "Zip" inside you text if it would get replaced by the assignment to the
variable of the same name? The way you do what you want is by concatenating
the variable into the text...

"URL;http://www.weather.com/weather/local/" & Zip & "lswe...etc"

Also, you cannot put a line continuation inside a text string... you have to
break the string and put the line continuation at the concatenation point to
join the strings back together. For example, if this was your string...

X = "Pretend this is a long text string that you want to break apart"

then you would do that like this...

X = "Pretend this is a long text string" & _
" that you want to break apart"

 

[Tim Williams]

"URL;http://www.weather.com/weather/local/" & Zip & _
"?lswe=" & Zip & _
"&lwsa=WeatherLocalUndeclared&from=searchbox_localwx", _
Destination:=Range("$A$2"))

Tim

 

[L. Howard Kittle]

Hi Rick and Tim

Thanks for looking at my post. I think I posed my problem incorrectly
and/or missunderstand you solutions.

This code works fine, for the hardcoded 97401 zip. (Of course it wrapped the
long code line here in this post but works okay when copied back into the VB
editor.)


Sub SomeWx()
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.weather.com/weather/loca...WeatherLocalUndeclared&from=searchbox_localwx"""
_
, Destination:=Range("$A$2"))
.PreserveFormatting = True
.WebTables = "15"
.Refresh BackgroundQuery:=False
End With
End Sub

This code does not work where I try to Dim Zip as the value of D1. D1 is a
drop down of zip codes.

Sub SomeWx()
Range("A1:C5").ClearContents
Dim Zip As Long
Zip = Range("D1").Value

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.weather.com/weather/local/& Zip &?lswe=& Zip
&&lwsa=WeatherLocalUndeclared&from=searchbox_localwx""" _
, Destination:=Range("$A$2"))
.PreserveFormatting = True
.WebTables = "15"
.Refresh BackgroundQuery:=False
End With

End Sub

Thanks for your time.
Howard

 

[Rick Rothstein]

You still have the object of the With statement constructed incorrectly. I
don't do QueryTables, so I can't vouch for the accuracy of your actual over
method call, but here is how it should look with the Zip variable
concatenated in...

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.weather.com/weather/local/" & Zip & "?lswe=" & _
Zip & "lwsa=WeatherLocalUndeclared&from=searchbox_localwx", _
Destination:=Range("$A$2"))

You should study it and the previous replies you have received in order to
understand how to work with text and concatenations (note in particular the
construction around both of Zip variable references to see how quote marks
are used to close off substrings).

 

[L. Howard Kittle]

Hi Rick,

" & Zip & " was the answer!! I thought I was on the right track but could
not get the " "s and the spaces and the &'s in proper form.

This seems to work for the few zips I have in the drop dkown.

Thanks for the help, always good info here from you guys and gals.

Regrds,
Howard