I
Islic
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
atabase
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 OLEDBon'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
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
atabaseLocking 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
on't Copy Locale on Compact=False;JetOLEDB: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