Insert Zip codes into weather code
 

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=searchbo x_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

Insert Zip codes into weather code
 

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"

--
Rick (MVP - Excel)


"L. Howard Kittle" wrote in message
...
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=searchbo x_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

Insert Zip codes into weather code
 

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

Tim

On Dec 15, 4:53*pm, "L. Howard Kittle" wrote:
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=searchbo x_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

Insert Zip codes into weather code
 

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/local/97401?lswe=97401&lwsa=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_local wx""" _
, Destination:=Range("$A$2"))
.PreserveFormatting = True
.WebTables = "15"
.Refresh BackgroundQuery:=False
End With

End Sub

Thanks for your time.
Howard

"L. Howard Kittle" wrote in message
...
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=searchbo x_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

Insert Zip codes into weather code
 

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_localw x", _
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).

--
Rick (MVP - Excel)


"L. Howard Kittle" wrote in message
...
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/local/97401?lswe=97401&lwsa=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_local wx""" _
, Destination:=Range("$A$2"))
.PreserveFormatting = True
.WebTables = "15"
.Refresh BackgroundQuery:=False
End With

End Sub

Thanks for your time.
Howard

"L. Howard Kittle" wrote in message
...
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=searchbo x_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

Insert Zip codes into weather code
 

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

"Rick Rothstein" wrote in message
...
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_localw x", _
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).

--
Rick (MVP - Excel)


"L. Howard Kittle" wrote in message
...
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/local/97401?lswe=97401&lwsa=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_local wx""" _
, Destination:=Range("$A$2"))
.PreserveFormatting = True
.WebTables = "15"
.Refresh BackgroundQuery:=False
End With

End Sub

Thanks for your time.
Howard

"L. Howard Kittle" wrote in message
...
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=searchbo x_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