Pivotcache problem using ado recordset into excel

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, viewBalan开发者_如何学GoceSheetType.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**

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

I know this is old, but just a thought. I noticed you were declaring two rs variables (one using ADODB and one using DAO) in the same subroutine. To be frank, I'm surprised the compiler didn't catch this for you, but I would suggest trying one or the other, but not both (and since you're initializing rs as ADODB.RecordSet, you should probably comment out rs = DAO.RecordSet).

I think that this error rise because your 'strSQL' is too long; Run your code with simpler strSQLbut I don't know how solve for long strSQL.

MHosseinput may have been onto something there. I have done this in excel many years ago and I notice that I split the sql into chunks and passed an array or arrays to the pivot object. Of course, there's no comment explaining why I did that and I don't remember any more, but it does work.

Set oPivCache = oWb.PivotCaches.Add(SourceType:=xlExternal) 
oPivCache.Connection = "OLEDB;" & sConnStr
oPivCache.CommandType = xlCmdSql oPivCache.CommandText = Array( _
    Array(LEFT(sqry, 200)), _
    Array(Mid(sqry, 201, 200)), _
    Array(Mid(sqry, 401, 200)), _
    Array(Mid(sqry, 601, 200)), _
    Array(Mid(sqry, 801, 200)), _
    Array(Mid(sqry, 1001, 200))) 
oPivCache.CreatePivotTable TableDestination:=oPVSht.Range("A1"), TableName:=sPvName