Question

页: 1 实时错误5:无效程序在以下线上以蓝色标示或论点:PT1.ChangePivotCache TheWorkbook.PivotCaches.Create (SourceType:=xlDatabase, SourceData:=PTSourceRngFcst, FCCC/SBI/2007/8/Add.1。

这些是法典所列细胞的价值观:

    G24 = \hguwarps03WarrenDataFinanceMANAGEMENT_REPORTSReforecast 2019P06_19-Jun Reforecast
    G25 = fncl-analysis-data_May-29.xlsx
    G26 = L:FinanceMANAGEMENT_REPORTSReforecast 2019P06_19-Jun Reforecast[fncl-analysis-data_May-29.xlsx]fncl-analysis-data

Sub Update_Sources()
 
Dim wb As Workbook, wbFromFcst As Workbook, wbFromReFcst As Workbook
Dim wkshtSourceFcst As Worksheet, wkshtSourceReFcst As Worksheet
Dim fromPathFcst As String
Dim SourceNameFcst As String
Dim PTSourcePathFcst As String
Dim rng As Range
Dim StartPointFcst As Range
Dim PTSourceRngFcst As String
Dim PT1 As PivotTable


Set wb = ThisWorkbook


fromPathFcst = Sheets("CONTROLS").Range("G24")
SourceNameFcst = Sheets("CONTROLS").Range("G25")
PTSourcePathFcst = Sheets("CONTROLS").Range("G26")


Set wbFromFcst = Workbooks.Open(fromPathFcst & SourceNameFcst)
Set wkshtSourceFcst = wbFromFcst.Sheets("fncl-analysis-data")
Set StartPointFcst = wkshtSourceFcst.Range("A1")
Set rng = wkshtSourceFcst.Range(StartPointFcst, 
StartPointFcst.SpecialCells(xlLastCell))
PTSourceRngFcst = PTSourcePathFcst & "!" & 
rng.Address(ReferenceStyle:=xlR1C1)


Set PT1 = 
ThisWorkbook.Sheets("Pivots").PivotTables("Customer_Cat_LocnType")


PT1.ChangePivotCache 
ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, 
SourceData:=PTSourceRngFcst, Version:=6)
 This is where I get the error







End Sub

Answer 1

查阅以下代码,以制定<代码>PivotCache,然后更新Pivot-Table,并更新。 PivotCache

Dim PT1 As PivotTable
Dim PTCache As PivotCache  <-- define new Pivot-Cahce Object

  set the Range Address as String
PTSourceRngFcst = Rng.Address(False, False, xlA1, xlExternal)

  set the Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PTSourceRngFcst)

  set The Pivot Table object
Set PT1 = ThisWorkbook.Sheets("Pivots").PivotTables("Customer_Cat_LocnType")

  refresh the Pivot Cache
PT1.ChangePivotCache PTCache
PT1.RefreshTable

Answer 2

我的VBA文字也给变革PivotCache线留下了同样的错误,在其他地方找不到解决办法,以便今后参考。在我的案件中,它证明,我试图改变的PivotCache的纸张表与另一个纸张表有着共同的过滤链接。我通过在管理文字之前把lic子与桌子脱钩来解决。然后,文字成功。

Sub decouple_pivot_table()

      A pivot table cache can only be changed if it does not share a filter
      connection with another table. If attempting to change the data source
      of a pivot table with a shared filter connection, the macro will throw
      a Run-Time error 5 for invalid procedure.
    
      Bottom Line: Not possible to have DIFFERENT data sources AND SHARED filter
    
    Dim PTtab As PivotTable
    Dim PTtabNew As PivotTable
    
    Debug.Print "Starting"
    Debug.Print ThisWorkbook.PivotCaches.Count
    
    With ThisWorkbook.Sheets("template")
    
        Set PTtab = .PivotTables("test")
        PTtab.TableRange2.Copy .Range("A25")
        
        .Range("A25").Activate
        ActiveCell.PivotTable.Name = "DecoupledTable"
        Set PTtabNew = .PivotTables("DecoupledTable")
        PTtabNew.ChangePivotCache _
            ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
            SourceData:=Range("data!A1:C100"), Version:=8)
        
        PTtabNew.RefreshTable
        
    End With
        
    Debug.Print ThisWorkbook.PivotCaches.Count
    Debug.Print "Passed"

End Sub

请注意,在试图人工改变数据来源时,这一错误信息要大得多:

不能改变与过滤控制相关的可塑性数据来源,这种控制也与其他可塑性数据连接起来。为了改变数据来源,首先将过滤控制与这种可使用或来自其他可使用物质的过滤控制分开。

友情链接