greyhound48
New member
- Local time
- Today, 07:24
- Joined
- Sep 13, 2005
- Messages
- 4
I have successfully built an excel pivot table from access,
but when it generates I have one problem.
My xlDataField(s) are displaying in order/move to row format (stacked) instead of order/move to column format (across)
Is there a way to define this in VBA? below is an example of the code I have used.
Private Sub exportToExcelPivot_Click()
Dim XlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlPT As Excel.PivotTable
Dim DataRange As String
Dim FileName As String
Dim Filepath As String
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
'enter filepath
Filepath = "H:/MyAccessOutput/"
FileName = "MyFileName " & Format(Date - 1, "yyyy-mm-dd") & " EOD.xls"
ExcelFile = Filepath & FileName
Const DataTable = "PODS"
' Delete file if it exists
If fso.FileExists(ExcelFile) Then
' Delete if not read only
fso.DeleteFile ExcelFile, False
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
DataTable, ExcelFile, True
Set TDef = CurrentDb.TableDefs(DataTable)
Set XlApp = New Excel.Application
XlApp.Visible = True
Set XlBook = XlApp.Workbooks.Open(ExcelFile)
With XlBook
DataRange = DataTable & "!" & .Worksheets(DataTable).UsedRange. _
Address(ReferenceStyle:=xlR1C1)
Set XlPT = .PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10)
With XlPT
.PivotFields(2).Orientation = xlPageField 'Region
.PivotFields(9).Orientation = xlPageField 'District
.PivotFields(1).Orientation = xlRowField 'REGION NAME
.PivotFields(10).Orientation = xlRowField 'DISTRICT NAME
.PivotFields(4).Orientation = xlRowField 'TERRITORY
.PivotFields(5).Orientation = xlDataField 'TOTAL EVENTS
.PivotFields(6).Orientation = xlDataField 'TOTAL EVENTS 2
.PivotFields(7).Orientation = xlDataField 'TOTAL EVENTS 3
.PivotFields(8).Orientation = xlDataField 'TOTAL EVENTS 4
.RowGrand = False
End With
End With
End Sub
but when it generates I have one problem.
My xlDataField(s) are displaying in order/move to row format (stacked) instead of order/move to column format (across)
Is there a way to define this in VBA? below is an example of the code I have used.
Private Sub exportToExcelPivot_Click()
Dim XlApp As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlPT As Excel.PivotTable
Dim DataRange As String
Dim FileName As String
Dim Filepath As String
Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
'enter filepath
Filepath = "H:/MyAccessOutput/"
FileName = "MyFileName " & Format(Date - 1, "yyyy-mm-dd") & " EOD.xls"
ExcelFile = Filepath & FileName
Const DataTable = "PODS"
' Delete file if it exists
If fso.FileExists(ExcelFile) Then
' Delete if not read only
fso.DeleteFile ExcelFile, False
End If
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
DataTable, ExcelFile, True
Set TDef = CurrentDb.TableDefs(DataTable)
Set XlApp = New Excel.Application
XlApp.Visible = True
Set XlBook = XlApp.Workbooks.Open(ExcelFile)
With XlBook
DataRange = DataTable & "!" & .Worksheets(DataTable).UsedRange. _
Address(ReferenceStyle:=xlR1C1)
Set XlPT = .PivotCaches.Add(xlDatabase, DataRange).CreatePivotTable( _
TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10)
With XlPT
.PivotFields(2).Orientation = xlPageField 'Region
.PivotFields(9).Orientation = xlPageField 'District
.PivotFields(1).Orientation = xlRowField 'REGION NAME
.PivotFields(10).Orientation = xlRowField 'DISTRICT NAME
.PivotFields(4).Orientation = xlRowField 'TERRITORY
.PivotFields(5).Orientation = xlDataField 'TOTAL EVENTS
.PivotFields(6).Orientation = xlDataField 'TOTAL EVENTS 2
.PivotFields(7).Orientation = xlDataField 'TOTAL EVENTS 3
.PivotFields(8).Orientation = xlDataField 'TOTAL EVENTS 4
.RowGrand = False
End With
End With
End Sub