Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic weather | Excel Discussion (Misc queries) | |||
Importing Weather Info | Setting up and Configuration of Excel | |||
Excel 2002 : How to insert a bank row between different codes ? | Excel Discussion (Misc queries) | |||
Weather Chart | Charts and Charting in Excel | |||
Weather counting query | Excel Discussion (Misc queries) |