Recorded VBA macro to create a PivotTable giving application-defined error

Question

A macro I recorded to create Pivot Table is giving me the following error:

Run-time error 1004: Application-defined or object-defined error.

I have deleted the relevant parts from the recorded macro as per this Microsoft link. However, I still receive the same error.

Sub testmacro()
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Sheet8!$A$1:$E$162"), Version _
        :=xlPivotTableVersion15).CreatePivotTable TableDestination:="", _
        TableName:="PivotTable27", DefaultVersion:=xlPivotTableVersion15
    With ActiveSheet.PivotTables("PivotTable27").CubeFields("[Range].[and not]")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable27").CubeFields("[Range].[term]")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable27").AddDataField ActiveSheet.PivotTables( _
        "PivotTable27").CubeFields("[Measures].[Count of family 2]"), "Count of family"
    With ActiveSheet.PivotTables("PivotTable27").PivotFields( _
        "[Measures].[Count of family 2]")
        .Caption = "Distinct Count of family"
        .Function = xlDistinctCount
    End With
End Sub

The debugger throws an error at the ActiveWorkbook.PivotCaches.Create line. A new worksheet does open, but it is blank. Thank you.

Answer 1

Short Answer - Define an external data source to point your Pivot Table at.

---

Longer Answer ...

When I record a macro to build a Pivot Table from a different workbook, I get the following ...

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "[JunkData.xlsx]Sheet1!R1C1:R11C3", Version:=6).CreatePivotTable _
    TableDestination:="Sheet6!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=6

You can see the SourceType:=xlDatabase is specified.

I can also set up the workbook JunkData.xlsx to be an external datasource. With the JunkData file closed, in Excel go to Data->Existing Connections->Connections on this computer->Browse for more ... navigate to JunkData, select it and follow the prompts, but only make the connection.

Then, I can record a new macro. On the Create Pivot table dialog, select "Use an external data source" and select JunkData.

I then get this line of code ...

ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
    ActiveWorkbook.Connections("JunkData"), Version:=6).CreatePivotTable _
    TableDestination:="Sheet7!R3C1", TableName:="PivotTable2", DefaultVersion _
    :=6

Your code looks like you should have SourceType:=xlDatabase instead of SourceType:=xlExternal.

In fact, if I change my first example to be SourceType:=xlDatabase, I get the same error as you (yes, I changed it to TableDestination:=""). The first example also will not run if the JunkData file is closed.

The second example works well (with the TableDestination modification).

If you need to create the Pivot Table from a closed file, I think you will need to make the external data connection.

Answer 2

I am adding another answer, because it is different enough from the first answer.

---

1. In order to use Distinct Count, you must select "Add this data to the Data Model" when inserting the Pivot Table.
2. This is equivalent to a specific Workbook.Connections.Add2 call in VBA.
3. Specifying a data model as the source for a Pivot Table causes the source to be identified as xlExternal, even though the data is in the same workbook.

The following macro works for me under a variety of scenarios (newly opened workbook; workbook opened and data model already exists). If I delete the .Add2 call, then I get the same error you were getting. I am able to get Distinct Count.

Dim NewSht As Worksheet
'
    Workbooks("Book2").Connections.Add2 "WorksheetConnection_Sheet1!$A$1:$D$20", "" _
        , "WORKSHEET;[Book2]Sheet1", "Sheet1!$A$1:$D$20", 7, True, False
    Set NewSht = Sheets.Add
    Debug.Print NewSht.Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
        ActiveWorkbook.Connections("WorksheetConnection_Sheet1!$A$1:$D$20"), Version _
        :=6).CreatePivotTable TableDestination:=NewSht.Name & "!R3C1", TableName:= _
        "myPivotTable", DefaultVersion:=6
    NewSht.Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("myPivotTable").CubeFields("[Range].[Name]")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("myPivotTable").CubeFields.GetMeasure "[Range].[Name]" _
        , xlSum, "Count of Name"
    ActiveSheet.PivotTables("myPivotTable").AddDataField ActiveSheet.PivotTables( _
        "myPivotTable").CubeFields("[Measures].[Count of Name]"), "Count of Name"
    With ActiveSheet.PivotTables("myPivotTable").PivotFields( _
        "[Measures].[Count of Name]")
        .Caption = "Distinct Count of Name"
        .Function = xlDistinctCount
    End With
End Sub