Add additional PivotTable 1

[lameid]

In the below code if the line in the else block runs it errors with "Application-defined or object-defined error".

This code is within a With block set to an Excel Application object automated from Acces. This code is part of a function that iteratively adds a pivottable to an Excel file. Through testing I know workseet(1) will always be a pivottable in my scenario if there is a pivot cache (although a way to identify the worksheet with the pivottable would be better).
The function is iteratively called and strTableName is based on a concatenation of a unique number so that it changes for each pivot table added. The purpose of the If statement and branching Else is to eliminate file bloat from adding multiple pivotcaches from the same source.

Any ideas of what I am missing?

         If XLWorkBook.PivotCaches.Count = 0 Then
            XLWorkBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
               .Range(XLSheet.Range("A1"), XLSheet.Range("A1").SpecialCells(xlLastCell))).CreatePivotTable TableDestination:="", TableName:= _
                strTableName
            .ActiveSheet.PivotTableWizard TableDestination:=.ActiveSheet.Cells(3, 1)
         Else
            XLWorkBook.Worksheets(1).PivotTables( _
                1).PivotCache.CreatePivotTable TableDestination:="", TableName _
                :=strTableName
         End If

 

[lameid]

Here is a recordered macro that creates a simple pivottable based on an existing pivottable...

Sub Macro1()
'
' Macro1 Macro
'
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    ActiveWindow.SmallScroll Down:=-111
    [purple]ActiveWorkbook.Worksheets("Some_Sheet_Name").PivotTables( _
        "PivotTable2").PivotCache.CreatePivotTable TableDestination:="", TableName _
        :="PivotTable1", DefaultVersion:=xlPivotTableVersion10
[/purple]    
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Service_Level", _
        ColumnFields:="Date_ID"
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Contract_Volume")
        .Orientation = xlDataField
        .Caption = "Sum of Contract_Volume"
        .Function = xlSum
    End With
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

For my purposes the purple text looks like what i want to get the equivalent of... having played around in the immediate window at run time I have concluded that the selection is not the cause by automating the selection. I have also used the version constant in testing and not. Nothing seems wrong to me. Since I am starting with a pivotcache object in both lines of my code (If Vs. Else block of OP) I don't unsderstand why one works and the other does not. I have even returned properties of the pivotchae using the immediate window. I am dumbfounded on this one. If dealing with the large files wasn't causing Excel to dog out my skystem, I would give up.

 

[lameid]

The only allegedly working example I have found is here...

http://vbadud.blogspot.com/2008/05/create-additional-pivot-table-using.html

I don't see any functional differences between my code and this...

 

[SkipVought]

hi,

Why don't you have a DISTINATION specified for your new PT?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

Hi Skip.

I believe you are asking why the table destination property is a zero length string? When I recorded the macro, I specified a new Spreadsheet. Apparently leaving the table destination blank yields a new SS for the Pivot Table.

 

[SkipVought]

CreatePivotTable Method
See AlsoApplies ToExampleSpecificsCreates a PivotTable report based on a PivotCache object. Returns a PivotTable object.

expression.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)
expression An expression that returns a PivotCache object.

TableDestination Required Variant. The cell in the upper-left corner of the PivotTable report’s destination range (the range on the worksheet where the resulting PivotTable report will be placed). The destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

It is interesting that the recorded version of the code leaves it blank and that when I add the pivot table in the then block of the if statement in my OP.

Technically it is a value but I can see it bombing on that too.

Hopefully I'll get to revising and testing today.

 

[lameid]

So I changed the code to add a sheet first (XLRptSheet)...

This meant I had to use Worksheet #2 instead of #1. Also my original If block was based on recorded code. I think I was partially thrown by it using 2 lines to set up the properties of the PivotTable. Anyways it works now. Thanks Skip!

If XLWorkBook.PivotCaches.Count = 0 Then
     XLWorkBook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
               .Range(XLSheet.Range("A1"), XLSheet.Range("A1").SpecialCells(xlLastCell))).CreatePivotTable TableDestination:=XLRptSheet.Cells(3, 1), TableName:= _
                strTableName
     '.ActiveSheet.PivotTableWizard TableDestination:=.ActiveSheet.Cells(3, 1)
Else
     XLWorkBook.Worksheets(2).PivotTables( _
                1).PivotCache.CreatePivotTable TableDestination:=XLRptSheet.Cells(3, 1), TableName _
                :=strTableName
End If