>source

"Run-time error '5': Invalid Procedure call or argument at the line below in blue: PT1.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PTSourceRngFcst, Version:=6) 를 받고 있어요

이것은 코드에 나열된 셀의 값입니다 :

   G24 = \\hguwarps03\WarrenData\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P06_19-Jun Reforecast\
    G25 = fncl-analysis-data_May-29.xlsx
    G26 = L:\Finance\MANAGEMENT_REPORTS\Reforecast 2019\P06_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


  • 답변 # 1

    PivotCache 를 설정하려면 아래 코드를 시도하십시오  그런 다음 업데이트 된 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
    
    

관련 자료

  • 이전 http - TidHttp ver 105498에서 헤더 행을 명시 적으로 접어야합니까?
  • 다음 c# - WHERE를 사용하여 항목의 하위 집합을 가져온 다음 Lambda Expressions를 사용하여 조건에 따라 REMOVEALL을 가져옵니다