| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What wrong with defined variable?
Does anyone have any suggestions what wrong is the variable - Mywebsite on
following coding? The url cannot be assigned into Mywebsite variable. Does anyone have any suggestions? Thank you very much for any suggestions Eric Dim Mywebsite As String Set Mywebsite = "http://www.stata.com/help.cgi?macro" With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
Thank you very much for your suggestion
Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to ..Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
I don't see:
..Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
I forget the error has been fixed. Based on both following queries, the first
one can load the code for 1, but the second one cannot do it. Do you have any suggestions on what wrong it is and how to solve it? Thank you very much for your reply and suggestions I look forward to your reply Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
It seems to me that the query for url has been changed if the URL is combined
together. Do you have any suggestions on what wrong the url is by combining Mywebsite to form the query. Thank you very much for suggestions Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
5
HSBC HOLDINGS (5) 122.9 Open: 122.5 Volume: 16.41M P/E: 11.3 Day High: 124.1 Turnover: 2.02B Yield: 5.10% 0.40 (0.3%) Day Low: 122.2 52Wk High: 153.5 Mkt Cap: 1,449.06B As of16:06 14 Mar 2008 Prev. Close: 122.5 52Wk Low: 104 NAV: 72.742 Bid: 122.7 Ask: 122.8 Board Lot: 400 -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... This is the way to insert your variable. In this case the variable has to be a number If you need additional help on a project, you may contact me privately. I am a retired series 7 stockbroker. I do this sort of thing for my clients on a daily basis. You really don't want to ADD a new connecition. What you want to do is REFRESH an existing with the variable. Or, variables in a loop. mycode = 3 With ActiveSheet.QueryTables.Add(Connection:="URL;" & _ "http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" _ & mycode, Destination:=Range("A5")) 'As I said earlier, you must have this line .Refresh BackgroundQuery:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... It seems to me that the query for url has been changed if the URL is combined together. Do you have any suggestions on what wrong the url is by combining Mywebsite to form the query. Thank you very much for suggestions Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
Thank everyone very much for suggestions
Could you please tell me what if the variable is a text? How can I insert the variable into query? Thank everyone very much Eric "Don Guillett" wrote: This is the way to insert your variable. In this case the variable has to be a number If you need additional help on a project, you may contact me privately. I am a retired series 7 stockbroker. I do this sort of thing for my clients on a daily basis. You really don't want to ADD a new connecition. What you want to do is REFRESH an existing with the variable. Or, variables in a loop. mycode = 3 With ActiveSheet.QueryTables.Add(Connection:="URL;" & _ "http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" _ & mycode, Destination:=Range("A5")) 'As I said earlier, you must have this line .Refresh BackgroundQuery:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... It seems to me that the query for url has been changed if the URL is combined together. Do you have any suggestions on what wrong the url is by combining Mywebsite to form the query. Thank you very much for suggestions Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
In this case the variable is NOT text but it would work the same way. One such example where [sym] is a defined named range. Can also work for a string for a loop. "URL;http://finance.yahoo.com/q?s=" & [Sym] -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Thank everyone very much for suggestions Could you please tell me what if the variable is a text? How can I insert the variable into query? Thank everyone very much Eric "Don Guillett" wrote: This is the way to insert your variable. In this case the variable has to be a number If you need additional help on a project, you may contact me privately. I am a retired series 7 stockbroker. I do this sort of thing for my clients on a daily basis. You really don't want to ADD a new connecition. What you want to do is REFRESH an existing with the variable. Or, variables in a loop. mycode = 3 With ActiveSheet.QueryTables.Add(Connection:="URL;" & _ "http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" _ & mycode, Destination:=Range("A5")) 'As I said earlier, you must have this line .Refresh BackgroundQuery:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... It seems to me that the query for url has been changed if the URL is combined together. Do you have any suggestions on what wrong the url is by combining Mywebsite to form the query. Thank you very much for suggestions Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
Thank everyone very much for suggestions
Could you please give me any suggestions on what the sign "_" do in the query? Why do I need sign "_" on number for variable? but I don't need sign "_" on string for variable. Thank you very much for any suggestions Eric "Don Guillett" wrote: In this case the variable is NOT text but it would work the same way. One such example where [sym] is a defined named range. Can also work for a string for a loop. "URL;http://finance.yahoo.com/q?s=" & [Sym] -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Thank everyone very much for suggestions Could you please tell me what if the variable is a text? How can I insert the variable into query? Thank everyone very much Eric "Don Guillett" wrote: This is the way to insert your variable. In this case the variable has to be a number If you need additional help on a project, you may contact me privately. I am a retired series 7 stockbroker. I do this sort of thing for my clients on a daily basis. You really don't want to ADD a new connecition. What you want to do is REFRESH an existing with the variable. Or, variables in a loop. mycode = 3 With ActiveSheet.QueryTables.Add(Connection:="URL;" & _ "http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" _ & mycode, Destination:=Range("A5")) 'As I said earlier, you must have this line .Refresh BackgroundQuery:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... It seems to me that the query for url has been changed if the URL is combined together. Do you have any suggestions on what wrong the url is by combining Mywebsite to form the query. Thank you very much for suggestions Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What wrong with defined variable?
Eric -
From Microsoft Visual Basic Help: "line-continuation character The combination of a space followed by an underscore ( _) used in the development environment to extend a single logical line of code to two or more physical lines. However, you can't use a line-continuation character to continue a line of code within a string expression." - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Eric" wrote in message ... Thank everyone very much for suggestions Could you please give me any suggestions on what the sign "_" do in the query? Why do I need sign "_" on number for variable? but I don't need sign "_" on string for variable. Thank you very much for any suggestions Eric "Don Guillett" wrote: In this case the variable is NOT text but it would work the same way. One such example where [sym] is a defined named range. Can also work for a string for a loop. "URL;http://finance.yahoo.com/q?s=" & [Sym] -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... Thank everyone very much for suggestions Could you please tell me what if the variable is a text? How can I insert the variable into query? Thank everyone very much Eric "Don Guillett" wrote: This is the way to insert your variable. In this case the variable has to be a number If you need additional help on a project, you may contact me privately. I am a retired series 7 stockbroker. I do this sort of thing for my clients on a daily basis. You really don't want to ADD a new connecition. What you want to do is REFRESH an existing with the variable. Or, variables in a loop. mycode = 3 With ActiveSheet.QueryTables.Add(Connection:="URL;" & _ "http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" _ & mycode, Destination:=Range("A5")) 'As I said earlier, you must have this line .Refresh BackgroundQuery:=False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Eric" wrote in message ... It seems to me that the query for url has been changed if the URL is combined together. Do you have any suggestions on what wrong the url is by combining Mywebsite to form the query. Thank you very much for suggestions Eric [Can load the code = 1] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Cannot load the code = 1] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) "Gary''s Student" wrote: I don't see: .Refresh BackgroundQuery:=False in the post -- Gary''s Student - gsnu200773 "Eric" wrote: Thank you very much for your suggestion Do you have any suggestions on following error? and do you have any suggestions on how to fix it? Thank you very much for any suggestions Eric [Working] With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=1", Destination:=Sheets("Temp").Range("$A$1")) [Not working] Mywebsite = 1 With Sheets("Temp").QueryTables.Add(Connection:= _ "URL;http://www.quamnet.com/quote.action?quoteSectionCode=&stockCode=" & Mywebsite & """", Destination:=Sheets("Temp").Range("$A$1")) Error pointing to .Refresh BackgroundQuery:=False "Gary''s Student" wrote: Instead of: Set Mywebsite = "http://www.stata.com/help.cgi?macro" use: Mywebsite = "http://www.stata.com/help.cgi?macro" -- Gary''s Student - gsnu200773 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable sum from defined input | Excel Worksheet Functions | |||
| variable not defined | Excel Programming | |||
| variable not defined, ActiveWorkbook.name | Excel Programming | |||
| Can Step through but when run defines variable wrong | Excel Programming | |||
| why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Linear Mode
