W
Webtramp
I have strange phenomenon when I create a pivot table. From time to time the
procedure throws the error 1004 as soon as the pivot table should be created
from the cache.
I have many different files with the same code. In each of these files I
create three pivot caches and based on them two tables are being created from
each cache..
The source files are closed.
Following my code to create the caches and tables. I am using two arrays to
get it all dynamically done.
Set PivCache = wkb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & strPathAssist & cstrAssistFolder & "\[assistexport_" &
Left(astrPivots(b, 0), Len(astrPivots(b, 0)) - 1) &
".xls]Assist_Stunden'!R1C1:R8000C30" _
)
'Erzeuge die Pivottabellen
For c = LBound(astrPivots, 2) To UBound(astrPivots, 2)
Set Pivottest =
PivCache.CreatePivotTable(TableDestination:=wks.Range("A4").Offset(0,
aOffsets(b, c)), TableName:=astrPivots(b, c))
This works well.
However from time to time I do get the above mentioned error. If for
instance one user uses the function to split the windows (this could also be
fixing the window or other Excel functions) and then saves the source file
with this option, then I get this error every time the procedure enters the
line I create my pivot tables.
If I leave the source file opened while the procedure runs, everything works
fine, every time.
Öffne ich die Quelldatei vor dem Ausführen der der Funktion geht alles
problemlos.
If I play around with the source file, this could be making all columns
visible on all sheets and click around it might work again even if the source
file is closed. However this only works sometimes. Once it helped to delete a
specific row until another person split the windows again.
I already tried to copy all the data into a new clean workbook. No changes.
Has anyone ever experienced such a phenomenon? Can anybody help me with this?
It does not make sense and I can not always ask the users to open all source
files before updating creating the tables.
procedure throws the error 1004 as soon as the pivot table should be created
from the cache.
I have many different files with the same code. In each of these files I
create three pivot caches and based on them two tables are being created from
each cache..
The source files are closed.
Following my code to create the caches and tables. I am using two arrays to
get it all dynamically done.
Set PivCache = wkb.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & strPathAssist & cstrAssistFolder & "\[assistexport_" &
Left(astrPivots(b, 0), Len(astrPivots(b, 0)) - 1) &
".xls]Assist_Stunden'!R1C1:R8000C30" _
)
'Erzeuge die Pivottabellen
For c = LBound(astrPivots, 2) To UBound(astrPivots, 2)
Set Pivottest =
PivCache.CreatePivotTable(TableDestination:=wks.Range("A4").Offset(0,
aOffsets(b, c)), TableName:=astrPivots(b, c))
This works well.
However from time to time I do get the above mentioned error. If for
instance one user uses the function to split the windows (this could also be
fixing the window or other Excel functions) and then saves the source file
with this option, then I get this error every time the procedure enters the
line I create my pivot tables.
If I leave the source file opened while the procedure runs, everything works
fine, every time.
Öffne ich die Quelldatei vor dem Ausführen der der Funktion geht alles
problemlos.
If I play around with the source file, this could be making all columns
visible on all sheets and click around it might work again even if the source
file is closed. However this only works sometimes. Once it helped to delete a
specific row until another person split the windows again.
I already tried to copy all the data into a new clean workbook. No changes.
Has anyone ever experienced such a phenomenon? Can anybody help me with this?
It does not make sense and I can not always ask the users to open all source
files before updating creating the tables.