On Mon, 31 Mar 2008 10:18:01 -0700, John V wrote:

I cobbled together a set of macros (below) to periodically query Yahoo
finance and download quotes. This spreadsheet would ideally run 24/7. I
noticed after a few weeks that it grew larger and slower. I discovered, under
Data Connections, about 7000 identical connections that had been created (and
saved). Just deleting them will be a massive chore, but avoiding them
entirely would be desirable.

With ActiveSheet.QueryTables.Add(Connection:="URL;" & yahoourl,

A couple of points: Every time you add a new QueryTable, you create a new
connection. You aren't deleting the existing QT before you add the new one.
However, you don't really want to delete the old and add the new. What you
want to do is change the properties of the old QT and refresh it. How much
work it is to change the properties is the subject of point two.

I assume the cells contain ticker symbols. If those cells never change, you
don't have to any coding at all. Just change the QT properties to refresh
automatically every two minutes.

If the symbols do change, then a little code will be required. In that case
you need to build the URL and change the Connection property of the
querytable. Here's a revised GetData sub for you to consider

Sub GetData2()

Dim rCell As Range, rSymb As Range
Dim sYahooUrl As String
Dim qt As QueryTable
Dim rQtStart As Range

Set rQtStart = Sheet1.Range("A30")
Set rSymb = Sheet1.Range("B2", Sheet1.Range("B29").End(xlUp))
sYahooUrl = "URL;http://quote.yahoo.com/d/quotes.csv?s=^GSPC"

'build url with column B
For Each rCell In rSymb.Cells
sYahooUrl = sYahooUrl & "+" & rCell.Value
Next rCell

sYahooUrl = sYahooUrl & "&f=nl1c"

'get a query table if it exits
On Error Resume Next
Set qt = rQtStart.QueryTable
On Error GoTo 0

If qt Is Nothing Then 'create new
Set qt = Sheet1.QueryTables.Add(sYahooUrl, rQtStart)
Else 'update existing
qt.Connection = sYahooUrl
End If

qt.Refresh False

'parse results
Application.DisplayAlerts = False
qt.ResultRange.TextToColumns _
Destination:=qt.ResultRange.Cells(1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Comma:=True
Application.DisplayAlerts = True

'reschedule update
StartTimer
Debug.Print Sheet1.QueryTables.Count

End Sub

If possible, you should start with a fresh workbook. You can copy the
contents of the sheet over, but you want to start with a workbook with no
querytables. With the above code, you should never have more than one.
--
Dick Kusleika
Microsoft MVP-Excel
http://www.dailydoseofexcel.com