Привет!
Есть книга Эксель с защищенными листами (выборочно настройки защиты для каждого листа).
- программно снимаю с этих листов защиту;
- обновляю сводную таблицу.
Но при изменении данных таблицы источника, старые отборы пропадают (я это свойство уже включил, чтобы не сохранялись неиспользуемые значения), иначе в списке фильтра остаются давно не использующиеся элементы.
Что я хочу:
- запоминать отмеченные элементы фильтра сводной таблицы перед обновление таблицы и очисткой её фильтра;
- восстанавливать значения отметок сохраненных элементов фильтра.
Что я уже сделал:
- отловил измение сводной таблицы;
- снимаю защиту с листов;
- создал массив, куда помещаю значения отбора (поля .Name, .Value, .Visible);
- программно обновляю своднюу таблицу на указанном листе;
- устанавливаю защиту каждого листа с индивидуальными для него флагами доступа.
Что у меня не получается:
- создать массив из структурированных элементов: String и Boolean, я умею только создать массив, в котором все элементы String.
Вот кусок кода моего поделия:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
Private iColumn
'--------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iArr() As String
Sheets("База данных план").Unprotect "***"
Sheets("План по областям").Unprotect "***"
'Теперь надо добраться до списка фильтра "Показатель"
With Sheets("План по областям").PivotTables("СводнаяТаблица2").PivotFields("Показатель").PivotItems
ReDim iArr(1 To .Count, 1 To 3) 'В последней строке фильтр "пусто"
For iColumn = 1 To .Count 'Для строк с установленным отбором (включенным флажком).
With .Item(iColumn)
iArr(iColumn, 1) = .Name 'В принципе, эти поля имеют одно и то же значение.
iArr(iColumn, 2) = .Value
iArr(iColumn, 3) = .Visible 'вот здесь у меня валится на последнем элементе коллекции,
'потому как Visible is Boolean, а у меня это строка
'iArr(iColumn, 2) = .Caption
'iArr(iColumn, 3) = .SourceName
'iArr(iColumn, 4) = .SourceNameStandard
End With
Next
End With
'Очистить список фильтра
Sheets("План по областям").PivotTables("СводнаяТаблица2").PivotFields("Показатель").ClearAllFilters
'Отметить флажками сохраненные значения фильтра, если они будут найдены
With Sheets("План по областям").PivotTables("СводнаяТаблица2").PivotFields("Показатель").PivotItems
For iColumn = 1 To .Count 'Для строк с установленным отбором (включенным флажком).
With .Item(iColumn)
SearchName = .Name
'.............. и как теперь это имя найти в массиве, я не знаю
End With
Next
End With
'Сформировать сводную таблицу
Sheets("План по областям").PivotTables("СводнаяТаблица2").PivotCache.EnableRefresh = True
Sheets("План по областям").PivotTables("СводнаяТаблица2").PivotCache.Refresh
Sheets("План по областям").Protect "***", DrawingObjects:=True, Contents:=True, AllowSorting:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
Sheets("База данных план").Protect "***", DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowInsertingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
Вот после строки
Sheets("План по областям").PivotTables("СводнаяТаблица2").PivotFields("Показатель").ClearAllFilters
я хочу снова перебрать коллекцию значений фильтра и сделать следующее:
- получить имя текущего фильтра;
- попытаться найти по этому имени поле в сохраненном массиве настроек фильтра;
- если элемент найден, то у текущего элемента установить флаг Visible, равный значению поля Visible в найденной строке массива;
- если элемент с таким именем отсутствует, то установить флаг Visible=false, пусть пользователь фидит только совпадающие с новыми значениями, фильтры.
Может, надо было взять и не массив для сохранения предыдущего элемента, а таблицу значений с типизированными колонками, а то и вовсе всю структуру настроек фильтра скопировать -- на данном этапе мне все равно, надо, чтобы работало.
