Run time error 1004 “Cannot open PivotTable source file”

2
votes

I want to create a pivot table based on a dataset (contained in a worksheet) in the same workbook.

The workbook is open when I run the macro. The dataset comes from running a query in Access, and then export it to excel. I also tried to save the workbook prior to running the macro. I am using excel 2016.

This is my code: 

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

The two last lines generates the same error message. "Run time error 1004. Cant open PivotTable source file 'C:\Users...'".

Does anybody know how to solve this problem? Thanks.

EDIT When I record a macro, VBA gives me this code (it works). 

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

I want my SourceData's range to be dynamically set. My efforts to do it generates (with debug.print): 'Parts & Machines2'!R1C1:R1328C14 It seems to be different from the macro-recorded :"Parts & Machines2!R1C1:R1328C14".

Is it this difference that generates the error that i cant find the source data?

Screenshot of Data.

Worksheet Data

2
vbaexcel

2 Answers

0
votes

I am not so sure where you define wsPartsMachines as Worksheet and where you set it.

However, the error is in the line:

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

If you add a line after:

Debug.Print pvtCache.SourceData

You will get 'Sheet3'''!R1C1:R6C3 in the immediate window - you have one ' too many. (I have used "Sheet3" as my SourceData) Try to modify this line to :

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

Edit 1: Try a different approach, bring the data source direclty as Range:

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")
0
votes

This code works. Still not sure why SourceData does not work. With small changes in the code that Shai Rado suggested it worked. Below is the code. 

Sub BusinessInteligenceCreatePivotTable()

    Dim PivotSheet As Worksheet
    Dim pvtCache As PivotCache
    Dim pvtTable As PivotTable
    Dim ws1PartMachines As Worksheet
    Dim pvtDataRng As Range

    'Determine the data range you want to pivot
    Set ws1PartsMachines = Worksheets("Parts & Machines2")
    ' set the Pivot Table Data Source Range
    Set pvtDataRng = ws1PartsMachines.Range("A1").CurrentRegion

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

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

    PivotSheet.Activate
    Range("A1").Select

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

End Sub