Creating a Pivot Table using VBA

[JKwan]

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.
[VBA]
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
[/VBA]
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]

You haven't added a data field - was that deliberate?

[JKwan]

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]

Try This:

[VBA]With PT.PivotFields(stgDataField)
.Orientation = xlDataField
.Function = stgFunction
.NumberFormat = stgNumFormat
End With[/VBA]

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]

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]

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]

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]

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: