Web Query question
 

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

Web Query question
 

I think you are mising (at least) one quote?

Reg Migrant

"David" wrote:

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

Web Query question
 

Maybe...

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))



David wrote:

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

--

Dave Peterson

Web Query question
 

Try this or send your file to me at the address below
Sub GetOptionsSAS()'Use to establish the query
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" & _
Range("b1") & "&m=" & Range("c1") & "", _
Destination:=Range("A3"))
.Refresh BackgroundQuery:=False
End With
End Sub
Name the sheet Options
Use below to refresh with different symbols in b1 and different months in
c1. Do NOT use the first one again.
Sub RefreshWithVariablesSAS()'Assign to a button on your sheet
With Sheets("Options").QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/op?s=" & Range("b1") &
"&m=" & Range("c1")
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "11,14,15,16,19"
'.WebPreFormattedTextToColumns = False
'.WebConsecutiveDelimitersAsOne = False
'.WebSingleBlockTextImport = False
'.WebDisableDateRecognition = False
'.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With
End Sub
If I can be of further assistance, contact me privately at the address below

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
I am trying to download stock options through the following vba code,
however
i am having problem with separator between "m" field which is month and
"s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

Web Query question
 

Hi Dave,

Thanks for the try, its not working, seems like with "" marks it thinks s=
range("b1")...

"Dave Peterson" wrote:

Maybe...

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))



David wrote:

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

--

Dave Peterson
.

Web Query question
 

I don't understand.

If you were looking for IBM, how would that line of code look?

I would have guessed:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& "IBM" & ";&m=2010-05", Destination:=Range("$A$11"))



David wrote:

Hi Dave,

Thanks for the try, its not working, seems like with "" marks it thinks s=
range("b1")...

"Dave Peterson" wrote:

Maybe...

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))



David wrote:

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

--

Dave Peterson
.


--

Dave Peterson

Web Query question
 

I would type IBM in cell B1, the line will look for IBM stock

"Dave Peterson" wrote:

I don't understand.

If you were looking for IBM, how would that line of code look?

I would have guessed:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& "IBM" & ";&m=2010-05", Destination:=Range("$A$11"))



David wrote:

Hi Dave,

Thanks for the try, its not working, seems like with "" marks it thinks s=
range("b1")...

"Dave Peterson" wrote:

Maybe...

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" _
& Range("b1").Value & ";&m=2010-05", Destination:=Range("$A$11"))



David wrote:

I am trying to download stock options through the following vba code, however
i am having problem with separator between "m" field which is month and "s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

--

Dave Peterson
.


--

Dave Peterson
.

Web Query question
 

Hi Don,

you are great, it works like a charm, not only i was able to figure out the
separator, and also you macro gave me better ideas, once again thank you

David

"Don Guillett" wrote:

Try this or send your file to me at the address below
Sub GetOptionsSAS()'Use to establish the query
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" & _
Range("b1") & "&m=" & Range("c1") & "", _
Destination:=Range("A3"))
.Refresh BackgroundQuery:=False
End With
End Sub
Name the sheet Options
Use below to refresh with different symbols in b1 and different months in
c1. Do NOT use the first one again.
Sub RefreshWithVariablesSAS()'Assign to a button on your sheet
With Sheets("Options").QueryTables(1)
.Connection = "URL;http://finance.yahoo.com/q/op?s=" & Range("b1") &
"&m=" & Range("c1")
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingAll
.WebTables = "11,14,15,16,19"
'.WebPreFormattedTextToColumns = False
'.WebConsecutiveDelimitersAsOne = False
'.WebSingleBlockTextImport = False
'.WebDisableDateRecognition = False
'.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With
End Sub
If I can be of further assistance, contact me privately at the address below

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
I am trying to download stock options through the following vba code,
however
i am having problem with separator between "m" field which is month and
"s"
field which is stock symbol, I appreciate any help i can get,

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q/op?s=" &
Range("b1").Value;&m=2010-05", Destination:=Range("$A$11"))

thanks
david

.