Hello,

can anyone help me ?

I wrote a VBA sub to create a pivot table based on external data.
when i run this sub the pivot table is created, the fields are OK (th
data field gets the right function & caption).
But, no data is calculated.
When i enter to the wizard of pivot table that was created and go bac
to "GET DATA" the MSquery pops up with the right data.
than i returned to Excel - without doing anything - and suddenly th
pivot table is calculated.

Because the pivot table is created and the right data is filtered
asume that the strings (connstring, querystring etc.) are ok.

the code:

'Sub ImportOB()

Sheets.Add.Name = "OB"
Sheets("OB").Activate

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = ConnString
.CommandType = TypeString
.CommandText = QueryString
.CreatePivotTable _
TableDestination:=Sheets("OB").Range("a1"), _
TableName:="OB"
End With

With ActiveSheet.PivotTables("OB")
.SmallGrid = False
.ColumnGrand = False
.RowGrand = False
.PivotCache.SavePassword = True
.PivotCache.BackgroundQuery = True
.AddFields _
RowFields:="ACCOUNTKEY", _
ColumnFields:="DEBITCREDIT"
End With

With ActiveSheet.PivotTables("OB").PivotFields("SUF")
.Orientation = xlDataField
.Function = xlSum
.Caption = "SumOpen"
.NumberFormat = "#,##0"
End With

ActiveWorkbook.ShowPivotTableFieldList = False
Application.CommandBars("PivotTable").Visible = False

End Sub

Thank you
Avne

--
Message posted from http://www.ExcelForum.com