By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,756 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Exporting to excel

P: n/a
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

Jul 18 '06 #1
Share this Question
Share on Google+
4 Replies


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

Jul 18 '06 #2

P: n/a

pi********@hotmail.com wrote:
You could record a macro in Excel that creates the pivot table, and
then call it using automation after you transfer the data.
I'm trying to automate the process.

Greg

Jul 18 '06 #3

P: n/a
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

Jul 18 '06 #4

P: n/a

Ron2006 wrote:
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
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

Jul 19 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.