Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Pivot
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 the 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 : -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Pivot
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 '------------------------------------------------ -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic pivot table linked to dynamic excel spreadsheets | Excel Worksheet Functions | |||
Dynamic pivot table | Excel Discussion (Misc queries) | |||
Dynamic formating a pivot | Excel Discussion (Misc queries) | |||
Dynamic Pivot tables | Excel Discussion (Misc queries) | |||
Dynamic pivot sum | Excel Discussion (Misc queries) |