automate pivot export to excel

[gebuh]

Hi all, I'm using access 2002. 2002 has pivoting, but in order to use it you have to download an xp add on(at least I did). After I installed the libraries pivots worked for me- and I added all manner of niftyness-before I discovered that no one else in my department could use it without adding the same downloads. So I decided to go with exporting to excel. That opened up a whole new can of worms, but in the end I finally got it working. I just wanted to share what I found with everyone here-maybe the next person won't have to work so hard. This code works from a toolbar button.
Of course if anyone has any suggestions, I'm interested.

Public Function goToPivot()
'automates creating a formatted pivotChart in excel from a query in access2002
'because of some trickiness with objects, no 'with's are used
On Error GoTo Err_goToPivot

    Dim xlApp As Excel.Application
    Dim XlBook As Excel.Workbook
    Dim XlPT As Excel.PivotTable
    Dim DataRange As String
    Dim ExcelFile As String
    Dim queryPivot As String

'set relative path and filename  of new spreadsheet
    ExcelFile = Application.CurrentProject.Path & "\xPivot.xls"
    queryPivot = "querypivotChartTest"
    
' Delete file if it exists
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(ExcelFile) Then
    ' Delete if not read only
        fso.DeleteFile ExcelFile, False
    End If

'export query to excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    queryPivot, ExcelFile, True
 
    Set xlApp = New Excel.Application
    xlApp.Visible = True
    Set XlBook = xlApp.Workbooks.Open(ExcelFile)
  
 'set style and range of cells, name pivotTable
    DataRange = queryPivot & "!" & XlBook.Worksheets(queryPivot).UsedRange. _
    Address(ReferenceStyle:=xlR1C1)
    Set XlPT = XlBook.PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
    TableDestination:="", TableName:="Pivot_Table1", _
    DefaultVersion:=xlPivotTableVersion10)
 
'create pivotChart, preload it with fields to get user started
    XlBook.Charts.Add
    XlBook.ActiveChart.Location xlLocationAsNewSheet, "RCA pivot"
    XlBook.ActiveChart.PivotLayout.PivotTable.AddDataField XlBook.ActiveChart.PivotLayout. _
    PivotTable.PivotFields("SIRs"), "Count of SIRs", xlCount
    XlBook.ActiveChart.PivotLayout.PivotTable.PivotFields("Team").Orientation = xlRowField
    XlBook.ActiveChart.PivotLayout.PivotTable.PivotFields("Team").Position = 1

    
'set axes and chart titles, size and fonts of pivotChart
    XlBook.ActiveChart.HasTitle = True
    XlBook.ActiveChart.ChartTitle.Characters.Text = "RCA DATA ANALYSIS"
    XlBook.ActiveChart.ChartTitle.Font.Bold = True
    XlBook.ActiveChart.ChartTitle.Font.Size = 18
    XlBook.ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
    XlBook.ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "CATEGORY"
    XlBook.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    XlBook.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "TOTAL"
    XlBook.ActiveChart.SizeWithWindow = True
            
      
 
'optional-create, save, then close
    'XlBook.Saved = True
    'xlApp.Quit
    'Set XlPT = Nothing
    'Set XlBook = Nothing
    'Set xlApp = Nothing
    
    
Exit_goToPivot:
    Exit Function

Err_goToPivot:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_goToPivot
 
End Function

 

[bluestuck]

heii, i just begin access vba.
how glad i am when i found this, thanks for everything.
but, when i put your code and trying to run it, iam getting error " 1004 - Unable to get the pivotFields property of pivottable class ".

what must i do to solve it ?
any help to answer this i really appreciate, thanks