Страницы: 1
RSS
Сводная таблица с данными из Oracle
 
Задача:
Создать сводную таблицу на основе данных из БД Oracle из объекта view.
Обновлять сводную таблицу по кнопке + возможность использования фильтров при получении данных из ячеек заполненных пользователем.

Что я сделал:
Создал книгу.
Создал заготовку сводной таблицы.
Добавил объект "кнопка ActiveX".
С помощью ADODB.Connection подключился к БД и научился выводить полученную информацию на листе.

В чем нужна помощь:
Не знаю как запихнуть ADODB.Recordset в PivotCache.
(Строки типа
Set pvt = ActiveSheet.PivotTables("Содная таблица5")
Set pvtCache = pvt.PivotCache
pvtCache.Recorset = rs(датасет с данными)
pvt.RefreshTable
не работаю, ошибка Run-time error -2147417848(80010108) The object invoked has disconnected from its clients)
Как корректно обновить PivotTable без пересоздания.
Как корректно использовать фильтры с данными из ячеек на листе в CommandText.


Дано: Windows 7, Excel 2007.
 
Код
Private Sub CommandButton1_Click()
 Dim ws As Excel.Worksheet
 Dim rg As Excel.Range
 Dim cn As New ADODB.Connection, cm As New ADODB.Command, rs As ADODB.Recordset
 Dim i As Integer
 Dim pt As Excel.PivotTable
 Dim pc As Excel.PivotCache

 Set ws = ActiveWorkbook.Sheets.Item(1)
 'ws.Range(ws.Cells(2, 1), ws.Cells(60000, 5)).ClearContents
  
  cn = "Provider=MSDAORA.1;Persist Security Info=False;Data Source=DATABASE;Extended Properties="""";User ID=USER_NAME;Password=PASSWORD"
  
  cn.Open
  cm.CommandType = adCmdText
  cm.CommandText = "select * from table"
  
  cm.ActiveConnection = cn
  Set rs = cm.Execute
  If rs.EOF Then
  MsgBox "Äàííûå îòñóòñòâóþò! ", vbOKOnly + vbCritical, "Óâåäîìëåíèå"
  End If
        
  Set pt = ws.PivotTables("СводнаяТаблица5")
  Set pc = pt.PivotCache

  Set pc.Recordset = rs
  pc.Refresh

  cn.Close

  Range("A1").Select
End Sub
 
А не проще сделать так: дать доступ к вьюхе на select пользователю, например report. В excel заходим в раздел данные - из других источников - из мастера подключения данных. Там выбираем поставщика OLE DB для Oracle. Указываем параметры подключения сервер, пользователь/пароль и если все введено корректно, нам предоставляется выбор вьюхи.
Далее на основе этих данным мы уже строим сводную таблицу, рядом можно поставить кнопку, назвав её обновить и присвоить для нажатия на кнопку следующий код -
Код
Sub Обновить()
ActiveWorkbook.RefreshAll
End Sub
 
Именно так сейчас и работает. Моя задача сделать возможность вычитки параметров из ячеек и использование их в запросе к БД. Как делать даже сложные запросы к БД средствами VBA или мастера я знаю, а как с параметрами нет (
 
Я делаю так, по другому мозгов не хватает:
Код
Sub Update_Table()
    With ThisWorkbook.Connections("Z-report").OLEDBConnection
        .BackgroundQuery = TRUE
        .CommandText = "Select * from (with DaDa as (select To_date('" & ThisWorkbook.Sheets("Z-Report").Cells(2, 15).Value & _
         "') BB, To_date('" & ThisWorkbook.Sheets("Z-Report").Cells(2, 16).Value & "') EE from dual )" & Chr(10) & ThisWorkbook.Sheets("SQL").Cells(2, 1).Value
    End With
    ThisWorkbook.Sheets("Z-Report").PivotTables(1).PivotCache.Refresh
    Application.EnableEvents = True
End Sub
Суть в чем:
1) на отдельном листе я сохраняю скрипт в одну или несколько ячеек (ThisWorkbook.Sheets("SQL").Cells(2, 1).Value)
2) потом всё это склеиваю с параметрами которые мне нужны
3) Обновляю сводник.
 
Интересный вариант, но в ячейке код сохранить не могу, он будет зашит в код, а еще лучше во вьюху в БД.
В моем варианте я получаю строкой Set rs = cm.Execute датасет уже с готовыми данными, а вот как это запихнуть в PivotCache моей сводной таблицы ума не приложу.
Основная проблема в этом. Думаю с параметрами из ячеек сам допру по вашему примеру.
 
Sylercool, так а как возможно будет получит параметры отбора???

Если я Вас правильно понял у вас есть скрипт который вы сохранили во View и Вы хотите с ней выбирать не все параметры, а только определенные
Например:
Скрытый текст
А потом: Select * from calendar where month = 10 (Где 10 можно получить в ячейку A4).
Напрямую скорей всего нельзя будет вгрузить в сводную. Можно просто выгрузить данные на лист
Код
Range(Cells(1, 4), Cells(1, 4)).CopyFromRecordset rs.DataSource
Потом их просто занести в сводную
Скрытый текст
И потом удалить лист с выгруженными данными ))
В сводной в кеше они останутся
Изменено: Xapa6apga - 10.08.2015 17:37:12
 
Да верно. Вьюшка будет запросом типа select field1, filed2, filed3 from temp_view. Параметры будут на этапе команды cm.CommandText.
Сам код запроса хранить в ячейке на листе нельзя, даже в скрытом виде. Выгружать на лист тоже нельзя - объемы данных буду порядка 1-5 миллионов записей. Зачем такое надо? Не спрашивайте... Excel не позволяет подобное хранить на листе, а вот напрямую из вьюшки получить такое в сводную можно. Вот и извращаюсь (
 
Вот как люди делают, как попробуете, скажите получилось или нет
 
Находил этот пример. Здесь создают новый PivotCache, а потом на его основе создают новую PivotTable. Я же не хочу пересоздавать объекты, а хочу заполнить и обновить существующие. При таких объемах данных боюсь, что пересоздавать объекты каждый раз непозволительная роскошь.
Вот тут:
Set pt = ws.PivotTables("СводнаяТаблица5")  
Set pc = pt.PivotCache
Set pc.Recordset = rs
pc.Refresh
я получаю в pt - сводную таблицу, в pc - ее кэш, а на строке Set pc.Recordset = rs(rs - датасет с данными) получаю ошибку. Не знаю как ее решить.
Изменено: Sylercool - 11.08.2015 12:47:52
 
Все работает ) Интересный конечно вопросик был ))
В макросе только подставите свой ConnectionString
Скорей всего у вас не создано подключение в сводной
 
Взял ваш код. Создал макрос. Создал простую кнопку. Присвоил кнопке макрос. На строке Set pc.Recordset = rs ошибка Run-time error '-2147417848(80010108)' Method 'Recordset' of object 'PivotCache' failed. Сам не понимаю, что за....
А ваш пример работает. Может дело в неправильной сводной? )))
Изменено: Sylercool - 11.08.2015 14:08:31
 
Проблема похоже именно в моей сводной таблице. Я создавал ее из диапазона данных. Как создать ее с пустым подключением пока не понимаю.
Еще момент - все поля и настроенные фильтры каждый раз будут сбрасываться? Можно ли по запросу обновлять только данные, не изменяя саму структуру сводной таблицы?
 
Цитата
Sylercool написал:
Я создавал ее из диапазона данных.
Если руками, Нужно создать connect, потом с этого подключения создать сводную
В примере который вы смотрели, там есть создание программно пост #9
Цитата
Sylercool написал:
Еще момент - все поля и настроенные фильтры каждый раз будут сбрасываться?
Они не будут сбрасываться если название полей будут одинаковые
 
Огромное спасибо! Работает! Сейчас буду разбиратся с параметрами и с их обработкой в коде.
Страницы: 1
Наверх