Run Time Error 1004：数据模型数据透视表无法获取PivotFields属性

问题诊断 #

你的代码触发Run Time Error 1004的核心原因有两个：

1. 未初始化wb变量：代码中尝试通过wb.Connections(ConnName)查找连接，但wb从未赋值，导致无法定位目标工作簿的连接集合。
2. 数据模型透视表字段引用错误：基于数据模型的透视表字段属于CubeFields对象，而非普通透视表的PivotFields，直接调用.PivotFields()会因对象模型不匹配报错。

另外，现有连接创建逻辑存在隐患：直接通过WORKSHEET连接字符串添加数据模型关联，不如先将数据转换为结构化表（ListObject）后再关联到数据模型稳定。

解决方案 #

1. 修正工作簿变量初始化 #

在With块内先将当前打开的工作簿赋值给wb：

Set wb = .

2. 使用CubeFields引用数据模型字段 #

数据模型透视表的字段需通过CubeFields访问，字段名称格式为[表名].[字段名]，其中表名是数据模型中自动生成的表名称（通常与工作表同名，若工作表有空格会自动替换为下划线）。

3. 优化数据模型连接方式 #

先将数据源范围转换为结构化表（ListObject），再添加到数据模型，这样能确保连接的稳定性和字段的正确映射。

修正后的完整代码 #

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 ListObj As ListObject
        Dim CubeFieldName As String
        
        Set wb = . '初始化工作簿变量
        Set Ws = .Worksheets(1)
        Set Rng = Ws.UsedRange

        '将数据源转换为结构化表并添加到数据模型
        On Error Resume Next
        Set ListObj = Ws.ListObjects("DataSourceTable")
        On Error GoTo 0
        If ListObj Is Nothing Then
            Set ListObj = Ws.ListObjects.Add(xlSrcRange, Rng, , xlYes)
            ListObj.Name = "DataSourceTable"
            ListObj.TableStyle = "TableStyleMedium2"
            '添加到数据模型
            ListObj.PublishToConnection True
        End If

        'Create a new sheet for the PivotTable
        Set NewSheet = .Worksheets.Add(Before:=Ws)
        NewSheet.Name = "testpivot"

        '获取数据模型连接
        ConnName = "WorksheetConnection_" & Ws.Name & "_DataSourceTable"
        On Error Resume Next
        Set conn = wb.Connections(ConnName)
        On Error GoTo 0

        'Now create a PivotCache from the Data Model
        Set PivotCache = wb.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)

        '引用数据模型中的字段（格式：[表名].[字段名]）
        CubeFieldName = "[DataSourceTable].[Testfield1]"
        With PivotTable
            'Add a field to the Filters area
            .CubeFields(CubeFieldName).Orientation = xlPageField
            .CubeFields(CubeFieldName).Position = 1
        End With

        Ws.Activate
    End With
End Sub

额外说明 #

• 若需要添加去重计数字段，可在创建透视表后通过以下代码实现：

  With PivotTable
      .AddDataField .CubeFields("[Measures].[Distinct Count of 目标字段名]"), "去重计数", xlSum
  End With
  

• 若不确定数据模型中的表名和字段名，可手动创建一个基于数据模型的透视表，然后通过VBA编辑器的本地窗口查看PivotTable.CubeFields集合的具体名称。

内容的提问来源于stack exchange，提问作者Alex M