Exporting to excel

Hi

I'm trying to download a query into excel and opening it up as a pivot
table. Firstly, is this possible? This is my code thus far:

Function Export()
Dim excel As String
excel = "CONSOLIDATION_XTRACT_TOTAL"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, excel,
"C:\Documents and Settings\GregoryCh\My Documents\Test.xls"

End Function

Greg

You could record a macro in Excel that creates the pivot table, and
then call it using automation after you transfer the data.

pietlinden@hotmail.com wrote:I'm trying to automate the process.

Greg

I know I did that a while back. I believe what I did was downloaded the
data, then from access, opened the spreadsheet, whent to the pivot
table tab, and issued a refresh.

I believe I made a macro in excell to show me the steps needed and then
duplicated these instructions from within Access.

Ron

Ron2006 wrote:Well this is where I am at.

Function TsfrExcel()
Dim objXL As Object
Dim excel As String
Dim strFile As String

excel = "CONSOLIDATION_XTRACT_TOTAL"
strFile = "C:\Documents and Settings\gregorych\My
Documents\Test.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, excel,
"C:\Documents and Settings\GregoryCh\My Documents\Test.xls"
Set objXL = CreateObject("Excel.Application")
With objXL.Application
.Visible = True
.Workbooks.Open strFile

objXL.ActiveWorkbook.PivotCaches.Add(SourceType:=x lDatabase,
SourceData:= _
"CONSOLIDATION_XTRACT_TOTAL!C1:C7").CreatePivotTab le
TableDestination:="", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10
objXL.ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
objXL.ActiveSheet.Cells(3, 1).Select
objXL.ActiveSheet.PivotTables("PivotTable3").AddFi elds
RowFields:=Array( _
"Fund_Type_2", "Fund_Type", "Expenditure_Type"),
ColumnFields:="Sub Hierarchy"
With
objXL.ActiveSheet.PivotTables("PivotTable3").Pivot Fields("SumOfTotal_Budget")
.Orientation = xlDataField
.Caption = "Sum of SumOfTotal_Budget"
.Function = xlSum
End With
objXL.ActiveSheet.PivotTables("PivotTable3").Pivot Select "YHR",
xlDataAndLabel, True
With obj.Xl.ActiveSheet.PivotTables("PivotTable3").Pivo tFields("Sub
Hierarchy")
.PivotItems("(blank)").Visible = False
End With
Set objXL = Nothing
End With

End Function

At the moment I am stuck here:
objXL.ActiveWorkbook.PivotCaches.Add(SourceType:=x lDatabase,
SourceData:= _
"CONSOLIDATION_XTRACT_TOTAL!C1:C7").CreatePivotTab le
TableDestination:="", _
TableName:="PivotTable3", DefaultVersion:=xlPivotTableVersion10

Greg