Webtramp
05-14-2007, 03:57 AM
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.
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.