Build Excel Pivot table in Access (Columns)

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
 
You can pivot data in Access using a crosstab query. Then export the crosstab.

Is there some reason you are building the pivot table in Excel? Access supports exactly the same functionality. You may find it easier to work with.
 
Automate pivot table in excel

I have previously built the pivot tables in Access which would be my preference. But my client wants the output of the pivot tables in Excel. Access requires a user to click on the "Export to Microsoft Office Excel" button and then save the file as an xls pivot table, but I want to cut out this manual step and automate this process so not one needs to click on anything.

Move to column is the last step I need to complete the spreadsheet, but it requires me to manually click on the stacked data column to do it. Is it possible to code this?

Any help is appreciated.
 
I assume so but I don't know how. When I don't know how to do something programatically with Word or Excel, I open Word or Excel and attempt to create a macro to perform the function. Then I open the macro and copy the generated code.
 
hi all,

I find this code that i use but I´m having a problem with a sum of on field,this get the count of the fields, please can you tell me how i get the sum of a field.

code that i find:
...
.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


tks a lot

checo
 
Hi,

I'm not sure that this is what you are talking about, but when I did something similar, my Access Pivot Table rows ended up as columns in the Excel version.

Off the top of my head I can't recall where it is, but there is a toggle to show results (details area) as columns or rows which solved my problem.

Sorry I can't be more precise right now but hope this helps. :)

If you can't find it, I will mess about and track it down when I'm back in the office on Sunday.
 

Users who are viewing this thread

Back
Top Bottom