Pivotcache problem using ado recordset into excel

Question

I'm having a problem with runtime error 1004 at the last line. I'm bringing in an access query into excel 2007. I know the recordset is ok as I can see the fields and data. Im not sure about the picotcache was created in the set ptCache line. I see the application, but the index is 0. Code is below...

Private Sub cmdPivotTables_Click()

    Dim rs As ADODB.Recordset
    Dim i As Integer
    Dim appExcel As Excel.Application
    Dim wkbTo As Excel.Workbook
    Dim wksTo As Excel.Worksheet
    Dim str As String
    Dim strSQL As String
    Dim rng As Excel.Range
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim ptCache As Excel.PivotCache

    Set db = CurrentDb()

    'to handle case where excel is not open
    On Error GoTo errhandler:
    Set appExcel = GetObject(, "Excel.Application")
    'returns to default excel error handling
    On Error GoTo 0
    appExcel.Visible = True
    str = FilePathReports & "Reports SCU\SCCUExcelReports.xlsx"
    'tests if the workbook is open (using workbookopen functiion)
    If WorkbookIsOpen("SCCUExcelReports.xlsx", appExcel) Then
        Set wkbTo = appExcel.Workbooks("SCCUExcelReports.xlsx")
        wkbTo.Save
        'To ensure correct Ratios&Charts is used
        wkbTo.Close
    End If
    Set wkbTo = GetObject(str)
    wkbTo.Application.Visible = True
    wkbTo.Parent.Windows("SCCUExcelReports.xlsx").Visible = True

    Set rs = New ADODB.Recordset
    strSQL = "SELECT viewBalanceSheetType.AccountTypeCode AS Type, viewBalanceSheetType.AccountGroupName AS AccountGroup, " _
                & "viewBalanceSheetType.AccountSubGroupName As SubGroup, qryAmountIncludingAdjustment.BranchCode AS Branch, " _
                & "viewBalanceSheetType.AccountNumber, viewBalanceSheetType.AccountName, " _
                & "qryAmountIncludingAdjustment.Amount, qryAmountIncludingAdjustment.MonthEndDate " _
            & "FROM viewBalanceSheetType INNER JOIN qryAmountIncludingAdjustment ON " _
                & "viewBalanceSheetType.AccountID = qryAmountIncludingAdjustment.AccountID " _
            & "WHERE (qryAmountIncludingAdjustment.MonthEndDate = GetCurrent()) " _
            & "ORDER BY viewBalanceSheetType.AccountTypeSortOrder, viewBalanceSheetType.AccountGroupSortOrder, " _
                & "viewBalanceSheetType.AccountNumber;"
    rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

'    Set rs = db.OpenRecordset("qryExcelReportsTrialBalancePT", dbOpenForwardOnly)

**'**********problem here
    Set ptCache = wkbTo.PivotCaches.Create(SourceType:=XlPivotTableSourceType.xlExternal)
    Set wkbTo.PivotCaches("ptCache").Recordset = rs**

Answer 1

Your Pivot Cache isn't name "ptCache", it's probably "PivotCache1" or something. Either of these will work, the former being preferred.

Set ptCache.Recordset = rs

Set wkbTo.PivotCaches(1).Recordset = rs

Answer 2

Hi Dick

Sorry to be so long getting back.

Here is what I'm trying to do. I have a query in access. I would like to take that query and put it in excel as a pivot table.

Ultimately, this is something I plan on doing many times, so I want to create a sub or function to accomplish the task.

I can take the query and put it in excel no problem. And I can then take that spreadsheet with the query and create a pivot table again no problem. And, I use a pivotcache with the create method to do that - only with different parameters as the data is not external.

Maybe I'm being stupid, as I have a solution. But putting the query onto a spreadsheet is not required - I plan to delete it after I get my pivot table. It would be nice to be able to get the pivot table directly from the recordset.

It seems to fail at the set ptCache line.

I find your answer a bit strange - I am using excel automation from Access - is that not the same as using excel itself?

Thanks for your efforts