PDA

View Full Version : Creating a Pivot Table using VBA



JKwan
09-11-2007, 08:58 AM
I am trying to create a Pivot Table using VBA and I am getting a blank Pivot Table. I don't know why. If I were to create it manually, works great, I do see data in the table. Maybe someone can point me to the right direction.

Set ptTable = wsSheet.PivotTables.Add( _
PivotCache:=ptCache, _
TableDestination:=wsSheet.Range("A4"), _
TableName:="PT_ADO")
'Manipulate some of the pivottable's properties.
With ptTable
.AddFields PageFields:="FIELD_OFFICE_CD"
.AddFields ColumnFields:=Array("LICENSE_NUMBER", "LINE_NUMBER")
.PivotFields("FIELD_OFFICE_CD").Orientation = xlPageField
' .PivotFields("LICENSE_NUMBER").Orientation = xlColumnField
' .PivotFields("LINE_NUMBER").Orientation = xlColumnField
' .Format xlTable2
End With

The above is the core of it. When the VBA excutes, my Table is created, but it is a blank, now, if I click on the drop down box of the pivot field, I see my data????
Thanks

rory
09-11-2007, 09:13 AM
You haven't added a data field - was that deliberate?

JKwan
09-11-2007, 10:01 AM
HMM, data field.... I thought I did with the ..AddFields and PivotFields. I do see them in the Pivot Table. Thanks for pointing me in the right direction, I will try to "add data field"

When you say DATA FIELD, if I understood you correctly, yes - it is deliberate. I don't need to see details. For the most part, all I needed was to "group" my data based on Field Offices or Licenses

I don't know if this makes any difference at all. I am pulling the data from a RecordSet, that I executed from a SQL command.

Thanks

Djblois
09-11-2007, 10:40 AM
Try This:

With PT.PivotFields(stgDataField)
.Orientation = xlDataField
.Function = stgFunction
.NumberFormat = stgNumFormat
End With

stgDataField would be what field you want in the data field
stgfunction would be what function do you want: Sum, AVG
stgNumFormat would be the formating you want the field to be

JKwan
09-11-2007, 10:55 AM
Djblois:
Thank you for the reply. I don't think what you posted is what I need. The outcome is still a blank

My data do show up in the Pivot Field, but they don't show up in the "grid"

Djblois
09-11-2007, 11:09 AM
To add a pivot Table you need to add at least one row field and at least one data field, Column fields aren't needed. So you would use that in addition to your code not to replace. I use a lot of variables in my code so it would hard for me post more of it. Also since you are adding from an external source there is more to it that I haven't worked with.

JKwan
09-12-2007, 06:16 AM
Djblois:
Thank you for your explanations. I will try to hammer at it more.

Out of curriosity... What if I ONLY wanted COLUMN data and NOT ROW data? Basically, I used the Pivot Table to group / slice and dice my data. I guess this is what Rory was pointing to - is it by design that I don't have "data field".

Djblois
09-12-2007, 11:59 AM
So far from my experience you always need to have row data. To visualize the way a pivot table works a little better here is a chart: