运行时错误1004“无法打开数据透视表源文件”

Question

我想基于同一工作簿中的数据集（包含在工作表中）创建一个透视表。

当我运行宏时，工作簿是打开的。数据集来自在Access中运行查询，然后将其导出到Excel。我还尝试在运行宏之前保存工作簿。我正在使用Excel2016。

这是我的代码：

Sub BusinessInteligenceCreatePivotTable()
    Dim PivotSheet As Worksheet
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable

    'Determine the data range you want to pivot
    Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsPartsMachines.Name & "'!" & wsPartsMachines.Range("A1").CurrentRegion.Address, Version:=xlPivotTableVersion15)

    'Create a new worksheet
    With ThisWorkbook
        Set PivotSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        PivotSheet.Name = "Production Schedule"
    End With

    PivotSheet.Activate
    Range("A1").Select

    'Create Pivot table from Pivot Cache
    'Set pvt = pvtCache.CreatePivotTable(TableDestination:=ActiveCell, TableName:="ProductionSchedule")
    Set pvt = PivotSheet.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=ActiveCell, TableName:="ProdSched")
End Sub

Sub BusinessInteligenceCreatePivotTable()
    Dim PivotSheet As Worksheet
    With ThisWorkbook
        Set PivotSheet = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        PivotSheet.Name = "Production Schedule"
    End With

    PivotSheet.Activate
    Range("A1").Select

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Parts & Machines2!R1C1:R1328C14", Version:=6).CreatePivotTable _
        TableDestination:=ActiveCell, TableName:="PivotTable1", DefaultVersion:=6

End Sub

资料截图。

Answer 1

我不太确定您在哪里定义WSPartsMachines作为工作表以及在哪里设置它。

但是，错误在行中：

Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsPartsMachines.Name & "'!" & wsPartsMachines.Range("A1").CurrentRegion.Address, Version:=xlPivotTableVersion15)

如果在：

Debug.Print pvtCache.SourceData

Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsPartsMachines.Name & "!" & wsPartsMachines.Range("A1").CurrentRegion.Address)

Dim pvtDataRng As Range

Set wsPartsMachines = Sheets("Parts & Machines2")
' set the Pivot Table Data Source Range
Set pvtDataRng = wsPartsMachines.Range("A1").CurrentRegion

Set pvtCache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=pvtDataRng)

'Create Pivot table from Pivot Cache
Set pvt = PivotSheet.PivotTables.Add(PivotCache:=pvtCache, TableDestination:=ActiveCell, TableName:="ProdSched")