What wrong with defined variable?

E

Eric

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"))
 
E

Eric

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
 
G

Gary''s Student

I don't see:
..Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


Eric said:
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 said:
Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
 
E

Eric

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 said:
I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


Eric said:
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 said:
Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


Eric said:
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 said:
Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Eric said:
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 said:
I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


Eric said:
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


:

Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Don Guillett said:
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
(e-mail address removed)
Eric said:
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 said:
I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


:

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


:

Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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
(e-mail address removed)
Eric said:
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 said:
I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


:

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


:

Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

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
(e-mail address removed)
Eric said:
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 said:
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
(e-mail address removed)
Eric said:
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"))


:

I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


:

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


:

Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
E

Eric

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 said:
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
(e-mail address removed)
Eric said:
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 said:
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
(e-mail address removed)
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"))


:

I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


:

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


:

Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 
M

Mike Middleton

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 said:
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 said:
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
(e-mail address removed)
Eric said:
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

:

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
(e-mail address removed)
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"))


:

I don't see:
.Refresh BackgroundQuery:=False

in the post
--
Gary''s Student - gsnu200773


:

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


:

Instead of:
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
use:
Mywebsite = "http://www.stata.com/help.cgi?macro"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help on macro coding fo URL 4
Help on macro coding for URL 2
What wrong is the coding? 5
What wrong with the coding? 4
How to use substitute function in macro? 4
Help on load the web link into Excel? 3
How to code macro with if condition? 2
How to use Index function in coding macro? 2

Top