Creating Excel Pivot Table from Access Query

[gazza110]

Hi,

I have been trying to generate a pivot table in MS Excel from an Access query without success. I can get the data into excel however cannot create the Pivot, keep getting error:

Run-time error '5': Invalid procedure call or argument

Private Sub cmdPTable_Click()

    Dim ExpCheck As Integer
    ExpCheck = MsgBox("Are you sure you want to export the Status Report?", vbExclamation + vbYesNo + vbDefaultButton2, "Status Report")
    
    If ExpCheck = 7 Then
    Exit Sub
    Else
    End If

    Dim MyDatabase As DAO.Database
    Dim MyQueryDef As DAO.QueryDef
    Dim MyRecordset As DAO.Recordset

    Dim i As Integer

    Set MyDatabase = CurrentDb
    Set MyQueryDef = MyDatabase.QueryDefs("StatusReport")

    Set MyRecordset = MyQueryDef.OpenRecordset

    Dim xlApp As Object
    Set xlApp = CreateObject("Excel.Application")
    With xlApp
    .Visible = True
    .Workbooks.Add
    .Sheets("Sheet1").Select
    .ActiveSheet.Range("A2").CopyFromRecordset MyRecordset
    .ActiveSheet.Range("A1:H1").Interior.ColorIndex = 37
    .ActiveSheet.Name = "Data"
   
    For i = 1 To MyRecordset.Fields.Count
        xlApp.ActiveSheet.Cells(1, i).Value = MyRecordset.Fields(i - 1).Name
    Next i
        xlApp.Cells.EntireColumn.AutoFit
        
    .ActiveWorkbook.Worksheets.Add
    .ActiveSheet.Name = "Analysis"
    .ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C1:R700C6").CreatePivotTable TableDestination:="Analysis!R3C1", tableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    
    End With
 
End Sub

Any suggestions where this is falling over?

It is the last line which is causing the error. The data exports into Excel and creates a new sheet called 'Analysis' but then fails to create the Pivot Table.

Many thanks
Gary