pivot table from access to excel

[davers5]

I want to be able to press a button on an access form and have it spit out an excel file with a pivot table (and some extra formatting). I've successfully managed to write code to do this from Excel (connect with the database, create a pivot table and add it to a new excel workbook) but when I try to do it from Access I'm having problems. I basically copied the code, added a procedure to open excel, and changed the connection properties. Below is my code. I get an error at this line (application defined or object defined error):

Set objPivotCache.Recordset = rstRecordset

Thanks for the help,

Dave

Sub PivotTable()

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim objPivotCache As PivotCache
Dim SQL As String
Dim ReportDate As Date

'get report date
ReportDate = CDate(Date)

' Open the connection.
Set cnnConn = CurrentProject.Connection

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn

'sql statement to return appropriate recordset
SQL = "SELECT Concatenate('ID',ID),[Order_Date], [Customer_Name], [Order_Number], "
SQL = SQL & "[Product_Description], [Units], [Full_Price] as [Price/Unit], [Discount] as Discount, "
SQL = SQL & "[Other_Price_Adjustment] AS Other, Concatenate('Name',[Name]) as [Sales Rep], "
SQL = SQL & "([Full_Price]*[Units])+([discount]+[other_price_adjustment]) AS [Total Price]"
SQL = SQL & "FROM tblInvoice, tblProduct, tblSalesperson, Rel_Commission_Percentage "
SQL = SQL & "WHERE [tblInvoice].[Product_ID] = [tblProduct].[Product_ID] "
SQL = SQL & "AND [tblInvoice].[ID] = [Rel_Commission_Percentage].[Order_Unit_ID] "
SQL = SQL & "AND [Rel_Commission_Percentage].[Salesperson_ID] = [tblSalesperson].[Salesperson_ID] "
SQL = SQL & "AND Month(Order_Date)=" & Month(ReportDate) & _
            " AND Year(Order_Date)=" & Year(ReportDate) & " "
SQL = SQL & "Group By Concatenate('ID',ID),[Order_Date], [Customer_Name], [Order_Number], "
SQL = SQL & "[Product_Description], [Units], [Full_Price], [Discount], [Other_Price_Adjustment],"
SQL = SQL & "([Full_Price]*[Units])+([discount]+[other_price_adjustment]),Name "
SQL = SQL & "ORDER BY [Order_Date], [Order_Number];"

With cmdCommand
    .CommandText = SQL
    .CommandType = adCmdText
    .Execute
End With

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand, , , adLockPessimistic
'Workbooks.Add
Call OpenExcel
' Create a PivotTable cache and report.
Set objPivotCache = objWkb.PivotCaches.Add(xlExternal)
Set objPivotCache.Recordset = rstRecordset

With objPivotCache
    .CreatePivotTable TableDestination:=objSht.Range("A22"), _
        TableName:="InvoiceData"
End With

With objSht.PivotTables("InvoiceData")
    .SmallGrid = False
    With .PivotFields("Product_Description")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Order_Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With .PivotFields("Total Price")
        .Orientation = xlDataField
        .Position = 1
    End With
End With
objSht.PivotTables("InvoiceData").Format xlTable7
objSht.PivotTables("InvoiceData").DataBodyRange.NumberFormat = "#,##0_);[Red](#,##0)"
Call PrintHeaderData(ReportDate, cnnConn, rstRecordset, cmdCommand)

' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
Set cnnConn = Nothing

End Sub

Public Sub OpenExcel()
    'set excel object variables
    
    Set objXL = New Excel.Application
    
    'keep the worksheet invisible until process finishes
    objXL.Visible = False
    
    'create new workbook and assign it to variable
    Set objWkb = objXL.Workbooks.Add
    
End Sub