ExcelBanter - Change a web query

Hi Don,

I would like to thank you for your help. I still use much of the code you
have helped with in the past.

I am actually walking down a list and bringing in many values. The query
moves the previous data over each time a new query is done. I was able to
overcome this in another macro, but not consistently, meaning that it will
work most of the time, but not all of the time. Is there a way to write this
query to Overwrite the destination and not move the data and also make this
happen with consistency?

The other problem I am having is that IEs cache is filling up and the macro
will simply stop working. Can I address the cache and delete it from VBA?

Thank you for your help.

David

"Don Guillett" wrote:

This is modified from a free file of mine that will do as many symbols as
desired for whatever period for month,week,or day for the adjusted close
value. Also, includes a graph of the history for each or all.
If you request OFF list I will send it to you.
Date Open High Low Close Volume Adj Close
5/27/2009 24.44 24.75 23.97 24.07 25870500 24.07

Change your date from c1 to a1 as column c will be deleted.
Sub getonesymbolandoneday()'SalesAidSoftware

Application.ScreenUpdating = False

'delete name buildup
For Each n In ActiveSheet.Names
n.Delete
Next
Columns("c:j").Delete

Set startdate = Range("a1")
StartMo = Month(startdate) - 1
StartDay = Day(startdate)
StartYr = Year(startdate)
StopMo = Month(startdate) - 1
StopDay = Day(startdate)
StopYr = Year(startdate)

myurl = "http://table.finance.yahoo.com/table.csv?a=" _
& StartMo & "&b=" & StartDay & "&c=" & StartYr & "&d=" _
& StopMo & "&e=" & StopDay & "&f=" & StopYr & "&y=0&g=" _
& [e2] & "&s=" & Range("a2") & ""

With ActiveSheet.QueryTables.Add( _
Connection:="URL;" & myurl, _
Destination:=Range("c3"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

'Puts into columns
Application.DisplayAlerts = False
Range("c3:c4").TextToColumns Destination:= _
Range("c3"), DataType:=xlDelimited, Comma:=True
Application.DisplayAlerts = True
Columns("c:j").AutoFit

Application.ScreenUpdating = True
[a2].Select
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"David" wrote in message
...
Hi Group,

I have a query:
Sub Macro3()
Range("C1").Select 'Range C1 Has a date ie 5/27/2009
ThisDate = ActiveCell.Value
ThisMonth = Month(ThisDate)
ThisDay = Day(ThisDate)
ThisYear = Year(ThisDate)
ActiveCell.Offset(1, 0).Select
z = ActiveCell.Offset(0, -2).Value 'This get a ticker symbol ie AA
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://table.finance.yahoo.com/table.csv?a=0&b=" & (ThisMonth) &
"&c=" _
& (ThisYear) & "&d=" & (ThisMonth) & "&e=" _
& ThisDay & "&f=" & ThisYear _
& "&y=0&g=w&s=" & z, Destination:=Range(ActiveCell.Address))
.WebSelectionType = xlSpecifiedTables
.WebTables = "20"
.Refresh BackgroundQuery:=False
End With
End Sub

C1 has a date in it ie 5/27/2009, Z capture a Ticker Symbol from cell A2.

I was using this in another macro and capturing Weekly data. I need to
change it so that it only capture one day. In this example 5/27/2009, but
I
need to do this with the variables, since the day will change often,
ThisDay,
ThisMonth and This Year.

Thank you for your help,
David