Блог

Я использую код XLS VBA для создания сводных таблиц на основе именованных диапазонов. Поскольку я получаю код ошибки при вызове CreatePivotTable, я заметил, что сводный кеш никогда не был создан. Я не могу понять причину. Есть ли способ глубоко погрузиться в сводные кеши.Создавать ошибки?

Я уже пытался определить SourceData

• с именованным диапазоном
• с абсолютными ссылками
• с относительными ссылками
• пошаговая замена каждого элемента его строковым значением

различные объявления ( wBk as Workboook , wSh as WorkSheet , kchPivot as PivotCache , piPivot as PivotTable DataSourceRge as Range , DataSourceString as String , , , , …)

 Set wBk = ActiveWorkbook
Set wSh = wBk.Sheets("MP_1_30")
Set DataSourceRge = Range("MP_1_30_Type_Ctl")
  

А) это не работает

 Set kchPivot = wBk.PivotCaches.Create (xlDataBase, DataSourceRge)
  

Б) это тоже не работает

 DataSourceString  = wSh.Name amp; "!" amp; _ 
DataSourceRge.Address(ReferenceStyle:=xlR1C1)

Set kchPivot = wBk.PivotCache.Create (xlDataBase, DataSourceString)
  

ВАЖНО: DataSourceRge — это диапазон в ОДИН столбец (примечание: все диапазоны, для которых я хочу автоматизировать создание сводных таблиц, имеют ширину в 1 столбец)

Я размещаю следующий элемент управления, чтобы убедиться в создании сводного кэша

 MsgBox wBk.PivotCaches.Count
  

Я всегда получаю «0» (ноль), но ожидаю «1»

Поскольку вы не получаете никаких сообщений об ошибках, я полагаю, проблема в том, что вы не создаете никаких «элементов отображения» (сводная таблица, сводная диаграмма и т.д.), Которые используют этот сводный кеш. Я провел некоторое тестирование с помощью кода. Вы должны иметь возможность копировать мои результаты с помощью прилагаемого кода и настройки данных, показанной на изображении ниже.

Используя новую книгу, добавьте образцы данных в столбец A листа 1, затем добавьте второй лист (Примечание: мой код использует кодовые имена для листов, поэтому, если вы не начинаете с совершенно новой книги, вам может потребоваться изменить кодовые имена в назначениях переменных листа

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

Когда я запускаю код, в первом MsgBox отображается «0»; в то время как во втором MsgBox отображается «1».

Хотя я не могу найти документацию по этому явлению; мое предположение заключается в следующем:

Чтобы помочь предотвратить раздувание файлов Excel, существует автоматизированный процесс, который либо удаляет неиспользуемые сводные кеши, то есть сводные кеши без каких-либо связанных элементов (сводная таблица, сводная диаграмма и т.д.), И / или предотвращает завершение сводного кэша до того, как с ним будет связан элемент отображения.

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

Если по какой-либо причине вы действительно не можете создать ни один из желаемых элементов отображения во время создания сводного кэша, я бы предложил создать «фиктивный» элемент отображения на очень скрытом листе, который можно удалить после добавления некоторых реальных элементов отображения в этот сводный кэш. (К вашему сведению: также в моем тестировании я попытался создать фиктивный элемент отображения, а затем сразу же удалил его. Это также привело к автоматическому удалению сводного кэша.)

 Sub test()
    Set wBk = ThisWorkbook
    Set wsh = Sheet1
    Set sht2 = Sheet2
    fRow = 1
    fCol = 1
    lRow = 5
    lCol = 1

    Set kchPivot = wBk.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=wsh.Range(wsh.Cells(fRow, fCol), wsh.Cells(lRow, lCol)))

    MsgBox wBk.PivotCaches.Count 'Does not increment for newly created pivotcache with no associated display items

    With kchPivot
        .CreatePivotTable TableDestination:=sht2.Range("D3"), TableName:="Performance"
    End With

    MsgBox wBk.PivotCaches.Count 'Increments for pivotcache now associated with a pivottable
End Sub