creating multiple pivots fails

[mark]

Hi.

I wrote an add-in that retrieves numerous parameters, extracts data from the
database, and returns a PivotTable, with none of the fields set... just the
outline of it.

This works fine, for creation of a single pivot table. However, I put it in
a loop to retrieve information for numerous projects, and it's failing at
this line:

pvC.CreatePivotTable TableDestination:="", TableName:=pvName,
DefaultVersion:=xlPivotTableVersion10

pvC is my PivotCache, pvName is the name of the pivot table.

The loop creates a new workbook, so each pivot table is in it's own Excel
workbook.

The error that I get is:

Application-defined or object-defined error

but if I hit Debug, and then hit the continue button (without changing
anything), it works. However, on the next run of the loop, I get the same
error, with no chance to Debug/Continue, and it stops.

However, if I put a Breakpoint into the code at this point, and manually
press the continue button for each run through the loop, it doesn't fail.

So, it appears that something is conflicting in the memory. I'd tried
putting in a DoEvents line, but it has no effect.

Can someone suggest something else to try? Thanks.

mark

 

[mark]

The loop creates a new workbook, so each pivot table is in it's own Excel

workbook.

I put in a line to close the workbook each time in the loop, and now the
program is continuing on with no troubles.

So, it's something about the connection to the previous pivot that's causing
the next one to fail?

 

[mark]

pvC.CreatePivotTable TableDestination:=""

still not sure I know or understand WHY it was failing, but I've found the
solution.

the TableDestination parameter is a variant. If I put in the default "" as
above, the routine fails at the second pivot table.

if I send it an specific range, such as this:

TableDestination:=Range("a1")

Then the program moves merrily along.