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