Ошибка 1004 при создании PivotCache.Recordset

[Cave_IAR]

Добрый день!
Следующий алгоритм должен создавать Recordset из запроса в .accdb базе Access, присваивать его кэшу сводной таблицы и потом строить саму таблицу по нему:

  Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim objXL As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objPivC As Excel.PivotCache
    Dim objPivT As Excel.PivotTable
    Dim objPivCh As Excel.Chart
    Dim fld As ADODB.Field
    Dim intCol As Integer
    Dim lngRow As Long
    Set rst = New ADODB.Recordset
    rst.Open strQrName, CurrentProject.Connection
'Запихиваем Эксель в память и создаем новый лист
    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Add
    Set objWS = objWB.ActiveSheet
    objWS.Name = strQrName & "_data"
'Создаем кэш Пивота, засовываем в него рекордсет и создаем пивотную таблицу из этого кэша
    Set objPivC = objWB.PivotCaches.Create(xlExternal, , 4)
    Set objPivC.Recordset = rst
    Set objPivT = objWS.PivotTables.Add(PivotCache:=objPivC, TableDestination:=ActiveSheet.Range("R1C1"), TableName:=strQrName)
'Настройка пивотной таблицы
...

Но на строке

Set objPivC.Recordset = rst

Выдает ошибку 1004: Application-defined and object-defined error.

Кто подскажет, из-за чего ошибка? Облазил пол-инета, в т.ч. забугорного, - нигде чёткого решения проблемы не нашёл.

Спасибо заранее за помощь!

[R Dmitry]

Все же необходимо сначала создать сам объект, а затем в него уже данные впихивать.

[Cave_IAR]

Сообщение от R Dmitry

Все же необходимо сначала создать сам объект, а затем в него уже данные впихивать.

Какой именно? Впрочем неважно, и objPivC, и rst, и все остальные объявлены в dim'е. И естесственно rst задействует некий запрос под именем strQrName.

[R Dmitry]

Сообщение от Cave_IAR

Какой именно? Впрочем неважно, и objPivC, и rst, и все остальные объявлены в dim'е. И естесственно rst задействует некий запрос под именем strQrName.

выделил +++++ основную Вашу проблему

Sub start()
Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim objXL As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objPivC As Excel.PivotCache
    Dim objPivT As Excel.PivotTable
    Dim objPivCh As Excel.Chart
    Dim fld As ADODB.Field
    Dim intCol As Integer
    Dim lngRow As Long
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
'+++++++++++++++++++++++++++++++++++++++++++++++++++++
Set cnn = CurrentProject.AccessConnection
'+++++++++++++++++++++++++++++++++++++++++++++++++++++
    rst.Open "select * from Q_TYPEPROBLEM", cnn
'Запихиваем Эксель в память и создаем новый лист
    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Add
    objXL.Visible = True
    Set objWS = objWB.ActiveSheet
    objWS.Name = "TestNameSheet"
'Создаем кэш Пивота, засовываем в него рекордсет и создаем пивотную таблицу из этого кэша
    Set objPivC = objWB.PivotCaches.Create(xlExternal, , 4)
    Set objPivC.Recordset = rst
    
    With objPivC
    .CreatePivotTable TableDestination:=objWS.Range("A1"), _
        TableName:="TestTable"
    End With

End Sub

запрос измените на свой

[Cave_IAR]

Сообщение от R Dmitry

выделил +++++ основную Вашу проблему

Sub start()
Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection
    Dim objXL As Excel.Application
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objPivC As Excel.PivotCache
    Dim objPivT As Excel.PivotTable
    Dim objPivCh As Excel.Chart
    Dim fld As ADODB.Field
    Dim intCol As Integer
    Dim lngRow As Long
    Set rst = New ADODB.Recordset
    Set cnn = New ADODB.Connection
'+++++++++++++++++++++++++++++++++++++++++++++++++++++
Set cnn = CurrentProject.AccessConnection
'+++++++++++++++++++++++++++++++++++++++++++++++++++++
    rst.Open "select * from Q_TYPEPROBLEM", cnn
'Запихиваем Эксель в память и создаем новый лист
    Set objXL = New Excel.Application
    Set objWB = objXL.Workbooks.Add
    objXL.Visible = True
    Set objWS = objWB.ActiveSheet
    objWS.Name = "TestNameSheet"
'Создаем кэш Пивота, засовываем в него рекордсет и создаем пивотную таблицу из этого кэша
    Set objPivC = objWB.PivotCaches.Create(xlExternal, , 4)
    Set objPivC.Recordset = rst
    
    With objPivC
    .CreatePivotTable TableDestination:=objWS.Range("A1"), _
        TableName:="TestTable"
    End With

End Sub

запрос измените на свой

Ура, спасибо большое) теперь понимаю, что впопыхах изучая Access незаслуженно пропустил тему Connections)
До этого получилось осуществить задуманное только копированием данных из Рекордсета на лист, созданием Сводной таблицы и созданием графика. Теперь, как я понимаю, процесс будет несколько быстрее проходить, особенно для крупных Recordset'ов)

[R Dmitry]

вот тут почитайте, в чем отличие
тынц
и обратите внимание сводную я добавил другим способом.