| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel file using unusual amunt of resources
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel file using unusual amunt of resources
To delete name build up (insertnamedefine)
For Each N In Sheet2.Names If InStr(N.Name, "ExternalData") 0 Then N.Delete Next N -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software "Bruce" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel file using unusual amunt of resources
Thanks Don,
I will have a go at doing this and let you know how I get on. bruce "Don Guillett" wrote: To delete name build up (insertnamedefine) For Each N In Sheet2.Names If InStr(N.Name, "ExternalData") 0 Then N.Delete Next N -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... 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 Microsoft MVP Excel SalesAid Software "Bruce" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving Excel file locks up all memory resources, accesses externalfile? | Excel Discussion (Misc queries) | |||
| Unusual File Size in Excel file | Excel Discussion (Misc queries) | |||
| Unusual Excel error message | Excel Programming | |||
| Unusual excel behaivor causes crash... Magically linked worksheets?? Why?! | Excel Programming | |||
Linear Mode
