Два способа создания объектов PivotCache и PivotTable
Я уже говорил, что объекты PivotCache и PivotTable можно создавать несколькими способами, и рассмотрел методы, используемые в этих способах. Теперь пришла пора привести соответствующие процедуры, решающие эти задачи. Я приведу две процедуры, в каждой из которых создаются оба эти объекты, но в каждой из них это делается по-разному. Вот код первой из этих процедур:
Public Sub CreatePivotCacheAndTable() 'Создание объекта PivotCache 'и на его основе - объекта PivotTable 'при заполнении данными используются 'свойства объекта: Connection,CommandText,CommandType Dim myCache As PivotCache Dim DbDir As String, DbPath As String DbDir = ThisWorkbook.Path DbPath = DbDir & "\dbPP2000.mdb"
Set myCache = ThisWorkbook.PivotCaches.Add(xlExternal) With myCache .Connection = "OLEDB; Provider=Microsoft.jet.oledb.4.0;" & _ "Data Source=" & DbPath .CommandType = xlCmdSql .CommandText = _ "SELECT Заказано.НазваниеКниги, Заказано.Стоимость, " & _ "Заказано.Количество, " & "Заказы.Заказчик, Заказы.Сотрудник, " & _ "Заказы.ДатаЗаказа" & Chr(13) & "" & Chr(10) & _ "FROM `C:\!O2000\DsCd\Ch18\dbPP2000`.Заказано Заказано, " & _ "`C:\!O2000\DsCd\Ch18\dbPP2000`.Заказы Заказы" & _ Chr(13) & "" & Chr(10) & _ "WHERE Заказано.КодЗаказа = Заказы.КодЗаказа" 'Создать отчет сводной таблицы With ThisWorkbook.Worksheets("Лист1") .Activate If .PivotTables.Count > 0 Then 'Очистить область сводной таблицы ClearRegion End If End With .CreatePivotTable TableDestination:=Range("A3"), _ TableName:="Анализ продаж"
End With End Sub
Обратите внимание, как создается объект PivotCache, - вначале он добавляется в коллекцию методом Add, но при этом объект не связан еще ни с каким источником данных и, следовательно, данных не содержит. На следующем этапе при заполнении свойств этого объекта - Connection, CommandType и CommandText - осуществляется связь с источником данных и выполняется команда, задающая запрос на получение данных. Соответствующий участок текста процедуры подсвечен.
Так кэш становится заполненным. После этого, к нему можно привязать и отчет сводной таблицы. В данном варианте для построения объекта PivotTable используется метод CreatePivotTable объекта PivotCache.
Заметьте, нельзя размещать сводную таблицу в области уже занятой другой сводной таблицей. По этой причине в процедуре проводится соответствующая проверка и область очищается, если она была занята. Приведу текст вызываемой процедуры ClearRegion:
Public Sub ClearRegion() Dim myr As Range Set myr = ActiveSheet.UsedRange myr.Clear End Sub
Надеюсь, она не требует особых пояснений. Напомню лишь, что свойство UsedRange возвращает всю область, занятую на рабочем листе. Эта область и очищается.
Давайте рассмотрим теперь другой способ создания объектов PivotCache и PivotTable. Приведу код процедуры для этого варианта:
Public Sub CreatePivotCacheAndTableWithADO() 'Создание объекта PivotCache 'и на его основе - объекта PivotTable 'Связь с базой данных осуществляется с использованием 'объектов ADO: Connection,Command,Recordset 'Объект Recordset является основой для построения кэша Dim myCache As PivotCache 'Соединение с базой данных и получение набора записей CreateRecordset Set myCache = ThisWorkbook.PivotCaches.Add(xlExternal) With myCache Set .Recordset = Rst1 'Создать отчет сводной таблицы With ThisWorkbook.Worksheets("Лист1") .Activate If .PivotTables.Count > 0 Then 'Очистить область сводной таблицы ClearRegion End If .PivotTables.Add PivotCache:=myCache, _ TableDestination:=Range("A3"), _ TableName:="Анализ продаж" End With End With End Sub
В этом варианте построения кэша сводной таблицы за доставку данных от источника данных в программу отвечают хорошо нам знакомые объекты ADO. Этот вариант имеет несомненные достоинства, поскольку объекты ADO обеспечивают эффективную доставку данных и, что не менее важно, способны получать данные из самых разнообразных источников данных, которые теперь можно использовать для построения сводных таблиц.
Работа с объектами ADO заканчивается построением объекта Recordset, задающего набор записей. Теперь остается ссылку на этот объект сделать значением одноименного свойства объекта PivotCache.
Так кэш становится заполненным. После этого, к нему можно привязать и отчет сводной таблицы. В данном варианте для построения объекта PivotTable используется метод Add коллекции PivotTables.
Чтобы закончить рассмотрение, остается привести текст вызываемых процедур CreateConnection и CreateRecordset:
Public Sub CreateConnection() 'Создание соединения с тестовой базой данных Access Dim strConnStr As String If Con1.State = adStateOpen Then Con1.Close 'закрыть соединение 'Конфигурирование соединения Con1 Con1.Provider = "Microsoft.jet.oledb.4.0" Con1.ConnectionString = "Data Source=c:\!O2000\DsCd\Ch14\dbPP2000.mdb" Con1.CursorLocation = adUseClient 'Открытие соединения Con1.Open End Sub
Public Sub CreateRecordset() 'Связывание с базой данных Access ' получение набора записей Dim strSQL1 As String 'Задание SQL оператора strSQL1 = _ "SELECT Заказано.НазваниеКниги, Заказано.Стоимость, " & _ "Заказано.Количество, " & "Заказы.Заказчик, Заказы.Сотрудник, " & _ "Заказы.ДатаЗаказа" & Chr(13) & "" & Chr(10) & _ "FROM `C:\!O2000\DsCd\Ch18\dbPP2000`.Заказано Заказано, " & _ "`C:\!O2000\DsCd\Ch18\dbPP2000`.Заказы Заказы" & _ Chr(13) & "" & Chr(10) & _ "WHERE Заказано.КодЗаказа = Заказы.КодЗаказа" CreateConnection 'задание свойств объекта Command Cmd1.ActiveConnection = Con1 Cmd1.CommandText = strSQL1 Cmd1.CommandType = adCmdText Cmd1.Prepared = True
'вызов команды на исполнение методом Execute Set Rst1 = Cmd1.Execute End Sub
Процедуры CreateConnection и CreateRecordset нам хорошо знакомы, - их аналоги уже появлялись в главе 15 при рассмотрении объектов ADO. При работе с ними, как и ранее, используются глобальные объекты - Con1, Cmd1, Rst1.
