Excel file using unusual amunt of resources

[Bruce]

Hi,

I have an Excel file that seems to use an unusual amount of resources. By
just opening the file it uses 50% of CPU power. If I leave it open for some
time it eventually hangs and i need to end the task.

I am running XPSP3, Off2k3 on a dual core2 3.2ghz with 4gb mem so resources
should be fine. The file is also only 2mb in size.

I have narrowed the problem down to 1 worksheet. If I delete this worksheet
the problem is fixed (but obviously I wish to keep it).

At this point I would appreciate any ideas on what is wrong with this
worksheet and how to fix it.

The sheet details
- Has only a few formulas and it not massive i.e. Report is in range A1 to Y42
- Has a query table
- The query table is refreshed with the following marco but does not update
automatically on open...

Sub getQuote()

Dim QuerySheet As Worksheet, DataSheet As Worksheet
Dim qurl As String, qStart As String
Dim i As Integer
Dim nQuery As Name

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set DataSheet = ActiveSheet
Set queryDef = Sheets("queryDef")
qStart = "C7"

Range(qStart).CurrentRegion.ClearContents

i = 7
qurl = "http://finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + queryDef.Range("B2")
queryDef.Range("B1") = qurl

QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range(qStart))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range(qStart).CurrentRegion.TextToColumns
Destination:=Range(qStart), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

Columns("C:C").EntireColumn.AutoFit

Application.DisplayAlerts = True
Application.ScreenUpdating = True
Range("A5").Select

End Sub


Any suggestions people?

Bruce

 

[Don Guillett]

I think I would establish the query and instead of adding one each time (not
bothering to erase the created name). I think you can probably do all
symbols, AT ONCE with ONE fetch, and then do your text to columns and go
from there. This is the sort of thing I do all the time. If desired, send
your file and I'll take a look.

 

[Don Guillett]

To delete name build up (insert>name>define)

For Each N In Sheet2.Names
If InStr(N.Name, "ExternalData") > 0 Then N.Delete
Next N

 

[Bruce]

Thanks Don,

I will have a go at doing this and let you know how I get on.

bruce