Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
4 out of 35 data connections running high cpu/memory
I have built a dashboard type of excel tool that has data connections
to 35 other excel files. The excel files are stored on a sharepoint site and users can go update the status of their task. I can use the refresh all and pull in updates. This has worked great for 3 years. This year I started running into all kind of memory errors. I included a URL at the bottom that really sounds like the problem I am running into. I don't understand how or why I am exceeding excel's memory/ ability. The files I am reading are only about 30k in size and the whole "tracker/dashboard" is about 35MB. I have tried about 25 different items to fix the problem and haven't had much luck. A few of the main items I have tried: changed all sheets formatting to 1 font 1 color. upgraded os to windows 7 - 64 bit and upgraded office from 07 to 2010 changed the refresh to do 1 sheet at a time copied all sheets to local drive, repointed data connections --- I did notice on all of the data connections have a command type of table, I assume that means its sucking in that whole table. There is a SQL option in the drop down but I haven't had luck in figuring out how to use it. when I watched taskmanager with doing the refreshing something odd caught my eye there are about 4 connections that are causing the problem, most of the refreshes take 2 seconds and memory/cpu barely move, but those 4 cause memory to spike from 300 MB or so to nearly 1.6 GB and cpu goes from 3% to nearly 25%. There has to be a better way of doing this or at least something I can do to get to root cause. The only way I can do the mass updates/refreshes now is to boot to safemode with networking enabled. Thank you for reading my long post I greatly appreciate any thoughts you might have!!! Will post in VBA also, there has to be a better way. Connection String is: Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C: \Documents and Settings\USERID\Desktop\holiday readiness\HRTask \Holiday Readiness - TEAM NAME - Director Name.xlsx;Mode=Share Deny Write;Extended Properties="HDR=YES;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False URL with information about a 2 GB memory limit in excel http://www.decisionmodels.com/memlimitsc.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste - Running out of Memory | New Users to Excel | |||
Excel macro running out of memory. | Excel Programming | |||
Running out of memory | Excel Programming | |||
XP running out of memory when using macro | Excel Programming | |||
Not enough Memory running macro | Excel Programming |