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.
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
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