Help on macro coding fo URL

[Eric]

Refering to the post under Excel Worksheet Functions

Does anyone have any suggestions on following macro coding?

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & myVariable is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do anyone have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

 

[Rick Rothstein \(MVP - VB\)]

I think you are missing an evaluated quote mark after your variable name.
See if this one works for you...

myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable & """", _
Destination:=sheets("Temp").Range("A1"))

Note the 4 quote marks... that should evaluate to a single trailing quote
mark when the statement is evaluated.

Rick

 

[Eric]

Thank you very much for your suggestions

There is some error on the variable Mywebsite, could you please tell me how
to define it and assign value into it?
Should I define the Mywebsite as a string like following code?
Dim Mywebsite As String
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
I try it, but error is still occurred.
Do you have any suggestions?
Thank you very much
Eric

I think you are missing an evaluated quote mark after your variable name.
See if this one works for you...

myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable & """", _
Destination:=sheets("Temp").Range("A1"))

Note the 4 quote marks... that should evaluate to a single trailing quote
mark when the statement is evaluated.

Rick

Refering to the post under Excel Worksheet Functions

Does anyone have any suggestions on following macro coding?

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & myVariable is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do anyone have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

 

[Rick Rothstein \(MVP - VB\)]

I have not worked with query tables before, so I don't know for sure. What I
did is compare your non-working statement to your working one and noted a
missing quote mark. My best guess is to simply declare the Mywebsite
variable as a String (like you did), but do not use the Set keyword when
assigning the value to it (Set is used for objects, not simple variables).
I'd give this a try...

Dim Mywebsite As String
Mywebsite = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Mywebsite & """", _
Destination:=sheets("Temp").Range("A1"))

and see if it works.

Rick

Thank you very much for your suggestions

There is some error on the variable Mywebsite, could you please tell me
how
to define it and assign value into it?
Should I define the Mywebsite as a string like following code?
Dim Mywebsite As String
Set Mywebsite = "http://www.stata.com/help.cgi?macro"
I try it, but error is still occurred.
Do you have any suggestions?
Thank you very much
Eric

I think you are missing an evaluated quote mark after your variable name.
See if this one works for you...

myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable & """", _
Destination:=sheets("Temp").Range("A1"))

Note the 4 quote marks... that should evaluate to a single trailing quote
mark when the statement is evaluated.

Rick

Refering to the post under Excel Worksheet Functions

Does anyone have any suggestions on following macro coding?

[Working]
With Sheets("Temp").QueryTables.Add(Connection:= _
"URL;http://www.stata.com/help.cgi?macro",
Destination:=Sheets("Temp").Range("$A$1"))

[Not working]
myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable, _
Destination:=sheets("Temp").Range("A1"))

It seems to me that
"URL;" & myVariable is not equal to
"URL;http://www.stata.com/help.cgi?macro".

Do anyone have any suggestions on how to solve it?
Thank you very much for any suggestions
Eric

 

[Don Guillett]

You forgot the .refresh and the dest needs to refer to the same sheet as the
query.

Sub getwebsitevariable()
myVariable = "http://www.stata.com/help.cgi?macro"
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & myVariable, _
Destination:=Range("A1"))
.Refresh BackgroundQuery:=False
End With