Web Query question

[David]

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

 

[Reg]

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

Reg Migrant

 

[Dave Peterson]

Maybe...

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

 

[Don Guillett]

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

 

[David]

Hi Dave,

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

 

[Dave Peterson]

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]

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

 

[David]

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