Блог

У меня есть электронная таблица, которая генерирует сводку запроса к базе данных в виде сводных диаграмм. Этот запрос к базе данных был написан третьей стороной и работает очень хорошо, за исключением того, что он удаляет все на листе и перезаписывает все данные на том же листе. Для работы с этим я написал макрос, который выполняет некоторую незначительную реорганизацию данных, а затем обновляет кэш сводных таблиц. Я бы хотел, чтобы сами сводные таблицы сохранялись, поскольку фильтры и параметры сводных таблиц не будут меняться между обновлениями.

Вот код, который я написал для достижения этой цели:

 Dim wb As Workbook
Dim sh As Worksheet
Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim SrcData As String

Set wb = ThisWorkbook
Set sh = wb.Sheets("Data")

'Find Last Row of Data
Lastrow = sh.Range("D" amp; Rows.Count).End(xlUp).Row    
' STEP 2 - Update Pivot Tables

' Set Source Data
SrcData = sh.Name amp; "!" amp; sh.Range("$A1:$CS" amp; Lastrow).Address(ReferenceStyle:=xlR1C1)
Set pvtCache = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, Version:=xlPivotTableVersion15)

' Change Pivot Cache to current Range and Refresh for all Pivot tables in Sheet
For Each sht In wb.Worksheets
    For Each pvt In sht.PivotTables
' ERROR 438 - Object Doesn't support Property or method
        pvt.ChangePivotCache (pvtCache)
        'sht.PivotTables(pvt).ChangePivotCache (pvtCache) ' Attempt 2
        'sht.PivotTables(pvt).ChangePivotCache (wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, Version:=xlPivotTableVersion15)) ' Attempt 2 - Longhand pvtCache
' ERROR 1004 - Method 'PivotTables' of object '_Worksheet' failed
        pvt.PivotCache.Refresh
    Next pvt
Next sht
  

Ошибка возникает в строке:

  pvt.ChangePivotCache (pvtCache)
  

В каждой ссылке, которую я видел, говорится, что это должен быть законный способ изменить сводный кэш, но каждый раз выдается сообщение «Ошибка 438 — Объект не поддерживает свойство или метод». Я также пытался сделать это вне цикла с помощью этого кода:

     ' Update WeekStops
Set sht = wb.Sheets("Stops by Week")
sht.PivotTables("WeekStops").ChangePivotCache (wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, Version:=xlPivotTableVersion15))
sht.PivotTables("WeekStops").PivotCache.Refresh
  

Ошибка 438 по-прежнему возникает при выполнении команды ChangePivotCache.

Может ли это быть связано с ошибкой 1004, которая возникает в следующей строке

 pvt.PivotCache.Refresh
  

когда я закомментирую строку над ним?

Спасибо и с наилучшими пожеланиями,

Джон

Проблема заключалась в создании pvtCache, внешнего по отношению к команде ChangePvtCache, а также в использовании круглых скобок в ChangePivotCache.

Рабочий код выглядит следующим образом:

 pvt.ChangePivotCache wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=SrcData, Version:=xlPivotTableVersion15)
  

Спасибо всем за вашу помощь.