VBA wording

dasare1503 · April 15th, 2004, 11:13 PM

I have this code:

With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://chart.yahoo.com/m?a=2&b=01&c=2001&d=2&e=31&f=2004&g=m&s=" & StockTicker, _
        Destination:=Range("A1"))

But the problem is the ActiveSheet part. Is there a way to make it more specific sort of like:

With Worksheets("Stocks").QueryTables.Add(Connection:= _
        "URL;http://chart.yahoo.com/m?a=2&b=01&c=2001&d=2&e=31&f=2004&g=m&s=" & StockTicker, _
        Destination:=Range("A1"))

Of course, if the above worked I wouldn't be asking here :)

Thanks!

chrislepingwell · April 16th, 2004, 04:43 AM

You could try prefixing with

Code:

ThisWorkbook.Worksheets("Stocks").QueryTables

or alternatively, if this is going to be a static sheet i.e. not something you create on the fly, you could give the sheet in question a 'code' name - you can do this in the properties window in the IDE. Say you name the sheet 'Stocks' you then referr to it like this

Code:

Stocks.QueryTables.Add(Connection:= _
        "URL;http://chart.yahoo.com/m?a=2&b=01&c=2001&d=2&e=31&f=2004&g=m&s=" & StockTicker, _
        Destination:=Range("A1"))

but I've just noticed that the real problem lies with the QueryTables destination range, which is defaulting to the active sheet (a common problem, with recorded macros).
Try this

Code:

With ThisWorkbook.Worksheets("Stocks")
      .QueryTables.Add(Connection:= _
        "URL;http://chart.yahoo.com/m?a=2&b=01&c=2001&d=2&e=31&f=2004&g=m&s=" & StockTicker, _
        Destination:=.Range("A1"))
End with

Chris

There are two secrets to success in this world:
1. Never tell everything you know