Access to Excel Pivot Creation

[misternumbertwo]

Hi Everyone,

I'm stuck in coding a database that creates an excel file with a pivot table. I am able to create the Excel object, export the data to it, perform some minor formating, add a new sheet to put the pivot table in.. However, when I open the created spreadsheet, it doesn't have any pivot in it. I am suspecting there's something wrong with my pivot cache code as I get no error when I compile and run the application.

Can anyone point to a solid code for this instance. I have tried for days to search online for the right code but to no avail before asking for help from you great guys. I am a newbie but exterting a lot of effort to learn

Here's the snippet of code that I am suspecting is wrong.

'Create pivot table
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
Dim FinalCol As Long
xlwkbk.Worksheets.Add().Name = "DataPivot"
xlwkbk.Save

' Define input area and set up a Pivot Cache
FinalRow = xlsheet.Cells(xl.Rows.Count, 1).End(xlUp).Row
FinalCol = xlsheet.Cells(1, xl.Columns.Count).End(xlToLeft).Column
Set PRange = xlsheet.Cells(1, 1).Resize(FinalRow, FinalCol)
Set xlsheet = xlwkbk.Worksheets("DataPivot")
Set PTCache = xlwkbk.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)
Set PT = PTCache.CreatePivotTable(TableDestination:=xlsheet.Cells(2, FinalCol + 2), _
TableName:="PivotTable1")
PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Workweek", "Freight Term"), _
ColumnFields:="Destination Organization Code"
With PT
.PivotFields("WorkWeek").Orientation = xlRowField
End With
xl.Sheets("DataPivot").Select
With xl.ActiveSheet.PivotTables("PivotTable1").PivotFields("Destination Organization Code")
.Orientation = xlDataField
.Function = xlCount
End With

'Save file
xl.ActiveWorkbook.SaveAs Filename:=Mydirectory & "\" & NewTableName & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

Thank you in advance everybody!

 

[chergh]

There a number of things wrong with the code I suspect a few you introduced when chopping your code up for posting here, e.g. setting the xlsheet object after you have already used it, there not being any data on your newly created sheet etc.

You are using this code in excel and not access?

Anyway I created some random data and eventually this produced a pivottable. Nothing really different.

Sub blah()
'Create pivot table
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range

Dim FinalRow As Long
Dim FinalCol As Long
'ThisWorkbook.Worksheets.Add().Name = "DataPivot"
ThisWorkbook.Save
Set xlsheet = ThisWorkbook.Worksheets("DataPivot")
' Define input area and set up a Pivot Cache
FinalRow = xlsheet.Cells(xlsheet.Rows.Count, 1).End(xlUp).Row
FinalCol = xlsheet.Cells(1, xlsheet.Columns.Count).End(xlToLeft).Column

Set PRange = xlsheet.Cells(1, 1).Resize(FinalRow, FinalCol)

Set PTCache = ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=PRange)

Set PT = PTCache.CreatePivotTable(TableDestination:=xlsheet.Cells(2, FinalCol + 2), TableName:="PivotTable1")

PT.ManualUpdate = True
' Set up the row & column fields
PT.AddFields RowFields:=Array("Workweek", "Freight Term"), _
ColumnFields:="Destination Organization Code"
With PT
.PivotFields("WorkWeek").Orientation = xlRowField
End With
'xl.Sheets("DataPivot").Select

With PT.PivotFields("Destination Organization Code")
.Orientation = xlDataField
.Function = xlCount
End With



End Sub