Excel crashes on PIVOT refresh

  • Thread starter Thread starter stork
  • Start date Start date
S

stork

I have an Excel spreadsheet that uses VBA to set the data source to its
crap string array of pieces of a SQL statement. The pivot table crashes
when it is refreshed. Excel GPFs.

WTF!

Is there anyone out there that actually has gotten pivot tables to
reliably work in Excel!

I see people having this problem on google with no answer or resolution
and all I get out of MSDN for searching on "Excel Pivot Table Crashes"
or "Excel Pivot Table unexpectedly" is a big fraking blank.

Are there any workarounds, is there any hope, or am I just stuck with
the same old usual.

Not that I am pissed off, but: Excel is a big piece of fraking crap.
Way to spend a $1000 on Office.

That's some quality work Microsoft. Thanks for the great upgrades.
How many upgrades will one have to do to get a working pivot table in
Excel? Is it 10 upgrades at 500 a piece? Is it 50?

Let's have a contest and see what year it will be before Microsoft gets
pivot tables that work. Hell, an Excel that actually works.

Let's see, Excel will not crash when:

a) Jesus comes back.
b) There is peace in the middle east.
c) We have a meaningful alternative to oil.
d) Toyota makes a car that genuinely sucks.
e) Government does something intelligent
f) We land a man on Mars
g) We go back to the Moon
h) There is a cure for any of Alzheimers, Paralysis, or World Hunger

Take your pick!
 
I ran into what may be the same problem with XL XP & earlier. The crashes
on pivot refresh went away when I upgraded to 2003. I assume because of the
increased RAM usage. You might want to check out
http://www.decisionmodels.com/memlimitsc.htm There's a lot of interesting
information on the other pages of that sight as well.

However, the workbook I had the problem with was very large and had over a
dozen pivots. I don't know if there is a correlation between my problems and
yours.

One thing I did as a workaround before upgrading was to turn off the default
"store data with table" option. That alone cut my memory usage in half
before the refresh. This worked fine for a while, but the project was
growing and we eventually started running into the same problem. Then we
upgraded and haven't had *that* problem since.

-RightClick on Pivot>TableOptions>Uncheck "Save data with table layout".

Another possiblity: what code are you using to set the pivot Datasource? I
had a set of code that was doing that without a problem until one day I
couldn't do a save of my "just refreshed" file without a crash. The code was
refreshing about 18 pivots. If I left one specific table "out of the loop"
the problem went away, and I could save the file. Once saved I could refresh
the remaining pivot and save again. Somehow, the refresh of that one table
put the workbook into an unstable state. I have since reworked the code to
use dynamic named ranges and it *seems* to have solved that problem, so far.

Post your code, if you like. But leave the invective out, please.

HTH,
 
I'm sorry for my ranting. This issue caused us to blow a deadline with
a client. The pivot tables in Excel were not our call, but we still
have a deliverable with it and I just wish Excel would either work or
fail gracefully. When I went to MS's web site and couldn't a word
about the Pivot tables crashing I just lost it. This has to be a
known issue to them and in the very least if they would post something
that says, "don't use pivot tables in this or that circumstance", and
instead there is a bunch of sales stuff.

It makes me crazy. sigh. I just want to get this stupid Excel monster
working enough so I can walk away from Excel and hopefully never have
to touch it again!

Thanks for the heads up.
 
George Nicholson said:
I ran into what may be the same problem with XL XP & earlier. The crashes
on pivot refresh went away when I upgraded to 2003. I assume because of the
increased RAM usage. You might want to check out
http://www.decisionmodels.com/memlimitsc.htm There's a lot of interesting
information on the other pages of that sight as well.

However, the workbook I had the problem with was very large and had over a
dozen pivots. I don't know if there is a correlation between my problems and
yours.

One thing I did as a workaround before upgrading was to turn off the default
"store data with table" option. That alone cut my memory usage in half
before the refresh. This worked fine for a while, but the project was
growing and we eventually started running into the same problem. Then we
upgraded and haven't had *that* problem since.

-RightClick on Pivot>TableOptions>Uncheck "Save data with table layout".

Another possiblity: what code are you using to set the pivot Datasource? I
had a set of code that was doing that without a problem until one day I
couldn't do a save of my "just refreshed" file without a crash. The code was
refreshing about 18 pivots. If I left one specific table "out of the loop"
the problem went away, and I could save the file. Once saved I could refresh
the remaining pivot and save again. Somehow, the refresh of that one table
put the workbook into an unstable state. I have since reworked the code to
use dynamic named ranges and it *seems* to have solved that problem, so far.

Post your code, if you like. But leave the invective out, please.

HTH,
Click to expand...

Great, thank for your advice I solved my trouble by tick on "save data with file"
Kieu Viet Huy
 
Back
Top