Search code examples
excelvbapivotpivot-table

Run Time Error 1004 'Unable to get the PivotFields property of the PivotTable class' with Data Model Pivot Table

I know this has been asked before but not in this specific case. I already tried for about an hour with GPT 4o.

I use the following test excel: one sheet with a small table as test data. enter image description here

When I execute the code below, I get the error mentioned in the title.

Sub debugger()

'Select the file to finish the Pivot table
Dim currentFolder As String
currentFolder = ThisWorkbook.Path
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.InitialFileName = currentFolder & "\"
fd.Show
'Extract file path and file name
Dim selectedFilePath As String
Dim selectedFileName As String
selectedFilePath = fd.SelectedItems(1)
selectedFileName = Dir(selectedFilePath)

'Open new copy workbook and adjust the data
With Workbooks.Open(selectedFilePath)
    'Create the Pivot
    Dim wb As Workbook
    Dim Ws As Worksheet
    Dim NewSheet As Worksheet
    Dim PivotCache As PivotCache
    Dim PivotTable As PivotTable
    Dim conn As WorkbookConnection
    Dim Rng As Range
    Dim ConnName As String
    Dim TestPath As String
    Set Ws = .Worksheets(1)
    Set Rng = Ws.UsedRange
    'Create a new sheet for the PivotTable
    Set NewSheet = .Worksheets.Add(Before:=Ws)
    NewSheet.Name = "testpivot"
    'Add the UsedRange to the Data Model by creating a WorkbookConnection
    ConnName = "DataModelConnection"
    On Error Resume Next 'Ignore if connection already exists
    Set conn = wb.Connections(ConnName)
    On Error GoTo 0
    If conn Is Nothing Then
        Set conn = .Connections.Add2(Name:=ConnName, _
            Description:="Connection to worksheet data", _
            connectionString:="WORKSHEET;" & .FullName, _
            CommandText:=Rng.Address(External:=True), _
                                    lCmdtype:=xlCmdExcel)
    End If
    'Now create a PivotCache from the Data Model (external connection)
    Set PivotCache = .PivotCaches.Create(SourceType:=xlExternal, SourceData:=conn, Version:=xlPivotTableVersion15)
    'Create the PivotTable from the data model connection
    Set PivotTable = PivotCache.CreatePivotTable( _
        TableDestination:=NewSheet.Cells(1, 1), _
        TableName:="testpivot", _
        DefaultVersion:=xlPivotTableVersion15)
    Ws.Activate

    With PivotTable
        'Add a field to the Filters area
        .PivotFields("Testfield1").Orientation = xlPageField
        .PivotFields("Testfield1").Position = 1
    End With

End With

End Sub

Specifically this piece of code:

With PivotTable
        'Add a field to the Filters area
        .PivotFields("Testfield1").Orientation = xlPageField
        .PivotFields("Testfield1").Position = 1
    End With
  1. The name of the PivotField should be correct (see test data)
  2. I specifically need the data model in order to access the distinct count function.
Solution

  • I recorded a macro to see how to add the fields. It appears that it is actually CubeFields Here is the macro refactored to use the OP's PivotTable variable. I recommend recording the a macro of you setting the table up the way you want and then have ChatGPT refactor it for you.

    With PivotTable.CubeFields("[Range 3].[Testfield1]")
    .Orientation = xlRowField
    .Position = 1
End With
With PivotTable.CubeFields("[Range 3].[Testfield2]")
    .Orientation = xlColumnField
    .Position = 1
End With
With PivotTable.CubeFields("[Range 3].[Testfield3]")
    .Orientation = xlColumnField
    .Position = 2
End With