Страницы: 1
RSS
Как поставить заполненый Recordset в источник сводной таблицы
 
Добрый день, ув. форумчане
Есть ли возможность обработать обьект Recordset "на лету"
и выгрузить данные на лист в уже нужном виде пока есть вот такой код,
который не отрабатывает в части SourceData:=rs. Возможно ли обойти проблему?
Код
Sub test27()
Dim strConn$, strSQL$, rs As Object, cn As Object, QTable As QueryTable, objPivotCache As Object
Set rs = CreateObject("ADODB.Recordset"): Set cn = CreateObject("ADODB.Connection")
Sheets("12").[A1].CurrentRegion.ClearContents
Sheets("Data").Activate
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=" & ThisWorkbook.FullName
strConn = strConn & ";Extended Properties=""Excel 12.0;HDR=true"";"
strSQL = "Select colPIB,colRegistrationTime, colProf From [Data$] As t1 Inner Join [ar$] As t2 On (t1.colTabNumber=t2.colTabNumber)"
cn.Open strConn
rs.Open strSQL, cn
'Set QTable = Sheets("12").QueryTables.Add(rs, Sheets("12").Range("A1")): QTable.Name = "Temp"
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rs, Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:=Sheets("12").[a3], TableName:="sd", DefaultVersion:=xlPivotTableVersion14
    With ActiveSheet.PivotTables("sd")
    With .PivotFields("colPIB")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("colRegistrationTime")
        .Orientation = xlRowField
        .Position = 2
    End With
    With .PivotFields("colProf")
        .Orientation = xlPageField
        .Position = 1
    End With
    End With
'QTable.Refresh
rs.Close:  cn.Close
Set cn = Nothing: Set rs = Nothing
ActiveWorkbook.Sheets("12").Activate
End Sub

 
 
Код
Set ActiveSheet.PivotTables("ВашаСводная").PivotCache.Recordset = rs
Спасибо
 
R Dmitry, выдает ошибку со стороны Екселя (1004) "Не возможно получить свойство PivotTables класса WorkSheet"
Я так понимаю поскольку физически сводной еще нет
Изменено: Vitallic - 27.04.2015 15:41:08
 
попробуйте сначала создать кэш, затем загрузить, а затем создать сводную
Код
Set ObjPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) 
Set ObjPivotCache.Recordset = Rs 
objPivotCache.CreatePivotTable TableDestination:=Range("A1") 
Спасибо
 
R Dmitry, что делаю не так (прилагаю файл)
почему то создается пустая сводная
смотрел  пример  отсюда но у меня и файл B.Key не отрабатывает
 
вы конструкцией With... End with ссылаетесь на PivotTables
- работайте с одной таблицей (созданной)
Код
Set PT = objPivotCache.CreatePivotTable(TableDestination:=Range("A1"))
With PT
   ...
End with
P.S.
не совсем понятно по коду, как должна выглядеть сводная и почему именно (ADO и rs), но вот ещё небольшой пример (можно Connection и Sql задать по коду)... пример немного оторван от ветки, поскольку "нужный вид" не был озвучен... для инфо вариант:
Код
With oSheet.Application.ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
            .Connection = sCon   'задать
            .CommandType = xlCmdSql
            .CommandText = sSQL   'задать
            .CreatePivotTable TableDestination:=oSheet.Range("A5"), TableName:=sPivotTableName
End With
        
        With oSheet.PivotTables(sPivotTableName)
            .SmallGrid = False   'может и не надо
            .MergeLabels = True   'может и не надо
            
            .PivotFields("Дата").Orientation = xlColumnField
            .PivotFields("Бренд").Orientation = xlRowField
            .PivotFields("Отгрузка, кг").Orientation = xlDataField
        End With
Изменено: JeyCi - 27.04.2015 18:05:58
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, спасибо за ответ. Пробую прикрутить Ваш кусочек кода к своим данным и возник такой вопрос -  можете показать как у Вас выгдядит строка подключения sCon ?
П.С.
Цитата
JeyCi написал: вы конструкцией With... End with ссылаетесь на PivotTables
так и Вы тоже работаете с коллекцией

Цитата
JeyCi написал:  With oSheet.PivotTables(sPivotTableName)
:)
 
Цитата
Vitallic написал: так и Вы тоже работаете с коллекцией
Нет.
PivotTables(sPivotTableName) - указывает на одну конкретную сводную с именем sPivotTableName. Это одно и тоже, что и PivotTables.Item(sPivotTableName)
Вы же использовали обращение именно ко всей коллекции сводных PivotTables.

Учитесь отличать элемент коллекции от всех её элементов(коллекции в целом).
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
The_Prist спасибо :) - только успела собраться отвечать, а вы меня подстраховали...
Vitallic, а вы бы сначала попробовали те строки вставить в ваш код - у меня на вашем файле сводная с полями появилась, но по какому принципу вы формируете поля я не знаю...
1) пример, который я привела - отсюда (пост№57) ... чтобы у вас и пример заполнения сводной был перед глазами - т.к. я не поняла, куда вы вставляете поля и почему так... там всего-то вставить строки, столбцы, данные...
2) пример Сводная таблица из нескольких листов (The_Prist)
3) примеры на англ сайте Contextures внизу страницы (KL - похоже, lapink2000)
... через OLEDB ещё не пробовала, через ODBC подключение стандартное
Код
sCon = _
    "ODBC;DSN=Excel Files;DBQ=" & sPath & "\\" & sWbName & ";" & _
           "DefaultDir=" & sPath & ";DriverId=790;" & _
           "MaxBufferSize=2048;PageTimeout=5;ReadOnly=1;"
если запрос по коду vba на открытую книгу - ребята, делают файл-дублёр... по ссылкам
P.S. ещё хорошая подборка линков по сводным на vba - на англ. сайте globaliconnect.com  
Изменено: JeyCi - 27.04.2015 20:10:32
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
вот такой может быть ваша сводная...
(не экспериментировала с вариантами без ADO - что вы спросили - то и приложила)
- только не понятно зачем вы делаете INNER JOIN, если в Сводной вы это Поле всё равно не используете...
p.s. если я не права, то строки, столбцы и данные расставьте, как надо... данные, если нужно не кол-во, а др - видимо, это не ваш случай, но если определитесь с видом сводной - можно задавать (в общем виде Сумма, например):
Код
.AddDataField .PivotFields("colProf"), "Сумма по полю colProf", xlSum   'хотя у вас тут время... надо его как-то складывать...
Изменено: JeyCi - 27.04.2015 20:51:05
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
JeyCi, благодарю за проявленное терпение и настойчивость (еще чуть-чуть подскажите)
По порядку:
- то что вариант с ADO это хорошо (так и задумано:));
- зачем я делаю запрос с INNER JOIN - использую эти операторы для  отсева ненужных данных (в примере третья фамилия в сводной не появляеться так как в таблице с условиями  (лист ar$) нет табельного номера этого человека, а значит его данные во внимание не принимаем;
- вид сводной который нужно прилагаю к сообщению (к сожалению подстановка в Ваш макрос кода из макрорекордера не дает желаемого результата)

Цитата
JeyCi написал: The_Prist спасибо  - только успела собраться отвечать, а вы меня подстраховали...
а разве я точно так же не обращаюсь к елементу коллекции в строке 14 кода из поста #1
Код
WithActiveSheet.PivotTables("sd")
 
Цитата
Vitallic написал: а разве я точно так же не обращаюсь к елементу коллекции
а разве я вам не давала ответ на пост№5?
все возможности использования сводных очертила, далее не вижу предметность разговора... т.к. сводная на основе ADO, насколько знаю, в принципе не имеет шансов на Обновление (кроме как макросом )  - посему интереса к теме уже нет, да и время иссякло... (нет интереса спорить и тем более доказывать очевидное)... успехов
Изменено: JeyCi - 30.04.2015 17:23:37
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
Решил проблему путем создания дополнительной сводной (не совсем то что хотелось - но результат достигнут)
такой метод гораздо быстрее чем создавать сводную без условий по данным, а потом циклом скрывать ненужные
(в моем случае когда позиций времени регистрации больше 10 000 это очень долго).

Всем спасибо
 
Цитата
JeyCi написал: (нет интереса спорить и тем более доказывать очевидное)...
... разве что в честь праздника 1-е Мая... как говорится "Мир.Труд.Май"...
с чем всех и поздравляю с пожеланиями успехов в этих 3-х словах...
странно, что вы не захотели адаптировать код поста№10... там всего-то придать PT вид 2007-го года и вывести ваши поля вашим способом... Сводной этот результат можно назвать очень с натяжкой - нет ни обновления, ни шапки верхней, ни полей данных, ни человеческих итогов [поскольку в значения вообще не вынесено ничего]...
... разве что для примера (модуль 3)
P.S. адаптация примера2 из поста9, конечно же, была бы намного интереснее... - без ADO и Rs, а с ODBC и sql в чистом виде
Изменено: JeyCi - 01.05.2015 10:51:21
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
через OLEDB тоже можно прямо вытаскивать набор из БД  в PivotCache, фильтруя согласно Sql... есть одна особенность .MaintainConnection = True ...

код для создания PT версии 2003 года во вложении... код для случая, когда БД находится в др файле - код прилагается для примера запроса через OLEDB... (чтобы проверить код, вложенный, - создать копию файла в ту же директорию, с тем же названием файла и к названию добавить "-1")
... т к запросы книги из самой себя имеют особенности - см варианты по ссылкам по созданию файлов-дублей... видимо, в ситуации OLEDB Подключения также для запросов книги из самой себя надо делать дубли... пример упрощённый, поэтому запрос к др книге...
p.s. Подключение к др книге устанавливается, запрос берёт нужные записи сразу в PivotCache... остальное в ваших руках (обновление по запросу ТС работает, !! только условия надо править в др книге по такому запросу)... всё остальное для доработки для собственного комфорта - (зависит от ситуации в целом -данные, их структура, алгоритм обработки, сам запрос, цели)...
кирпичик вот такой:
Код
strConn = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Data Source=" & f
strConn = strConn & ";Extended Properties=""Excel 12.0;HDR=true"";"
strSQL = "Select colPIB,colRegistrationTime, colProf From [Data$] As t1 Inner Join [ar$] As t2 On (t1.colTabNumber=t2.colTabNumber)"Sheets("Ëèñò2").Activate    '*****************************************************************************
    With ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        .Connection = strConn
        .CommandType = xlCmdSql
        .CommandText = strSQL  
        
        'If .QueryType = xlOLEDBQuery Then
        .MaintainConnection = True
        'You can set the MaintainConnection property only if the QueryType property of the query table or PivotTable cache is set to xlOLEDBQuery.
        
        Set PT = .CreatePivotTable(TableDestination:=Range("A1"))
    End With
Изменено: JeyCi - 01.05.2015 14:57:15
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
Страницы: 1
Наверх