Dynamic Pivot

  • Thread starter Thread starter cjorgensen
  • Start date Start date
C

cjorgensen

Hi,
I had success last time I posted a problem here, so I'll try again :D

I am creating a program that has several hidden sheets with larg
amounts of data. I created a login procedure that identifies the user
Then the program extracts user specific data from a hidden sheet, an
puts it in a "user data sheet". The program then creates a pivot on th
fly based on this data (this is done because the user *only shoul
access his/hers data* ).

It works perfectly unless the user is allowed to access large amount
of data - the pivot creation crashes since I reach the pivot cach
limit.

Is there:
1) A way to get the limitation (cache) from the users excel app?
2) A way to calculate the source datas use of cache BEFORE executin
the pivot generation

If this was possible I could create different "sets" of pivot field
based on the limitations in Excel, e.g. reduce columns/rows for user
with access to large amounts of data.

If you have other nice solutions to similar problems they are deepl
appreciated!

Have a nice day everyone :
 
Here is something to play with. Probably won't help much because if yo
test it by opening/closing workbooks the Free Memory mysteriously stay
the same whilst the Memory Used changes.

Doesn't give the same value as Task Manager either.

In the Windows 3.1 days Excel did not restore memory to the system o
closing, so merely opening and closing it without doing anything woul
crash the system after a few times.

'------------------------------------------
Sub EXCEL_MEMORY()
On Error Resume Next
PT = ActiveSheet.PivotTables(1).PivotCache.MemoryUsed
rsp = MsgBox( _
"Memory Used : " & Format(Application.MemoryUsed, "###,###")
vbCr & _
"Free Memory : " & Format(Application.MemoryFree, "###,###")
vbCr & _
"----------------------------------------" & vbCr & _
"Total Memory : " & Format(Application.MemoryTotal, "###,###"
& vbCr & _
"----------------------------------------" & vbCr & _
"PivotCache : " & Format(PT, "###,###") _
, vbOKOnly, " EXCEL MEMORY")
End Sub
'------------------------------------------------
 
Back
Top