L
Lisa Lowry
Greetings,
I am using Excel 2003, Windows XP. I am creating a pivot table
automatically with vba in a custom built addin. The code creates an
empty pivot pointing to a recordset. The users can access the field
list and create their pivot any way they choose (the point is to get
them the data). Works like a charm.
The problem: When a number field is dragged into the Data Area, the
decimals are not there. When checking the Field Settings, the format
is set to General. Changing the format to Number with 2 decimal places
changes the value to nnn.00 - the decimal values are missing.
Naturally this will cause some excitement as the amounts are not
accurate, expecially at the summary level.
Note: When I create a pivot using the pivot table wizard from the
menu, using the same SQL string to collect the data from Oracle, the
numbers in the data area have a General format but the decimals are
present (and visible). So the SQL is good. Looking at the two sets of
pivots, the decimals in the automated table are simply truncated, the
numbers have not been rounded. The text fields in the automated table
are behaving as expected.
The question: Is there a setting/property I can use to preserve the
decimal values? What am I missing? They can be hidden or visible, but
need to be there.
Here, in part, is the addin's automated code:
[I am passing a recordset (empty) to a function that pulls the data
from Oracle using a passed SQL string. The recordset is populated with
the data, and the pivot table is created using the recordset as the
source.]
Public sub s_CreatePivot
Dim rstADO As ADODB.Recordset
Dim objPivotCache As PivotCache
strSQLCall = "select * from schema.table_name"
'Get a recordset object.
Set rstADO = New ADODB.Recordset
'Open the recordset object with the passed SQL string
If f_FetchReportData(rstADO, strSQLCall) = True Then
'Assign the recordset to the NEW pivot cache and create a pivot table.
Set objPivotCache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = rstADO
With objPivotCache
..CreatePivotTable TableDestination:=Range("A4"), TableName:="MyPivot"
End With
End If
'Clean up
rstADO.Close
Set rstADO = Nothing
Exit Sub
I appreciate your time and suggestions.
Many thanks,
Misty
I am using Excel 2003, Windows XP. I am creating a pivot table
automatically with vba in a custom built addin. The code creates an
empty pivot pointing to a recordset. The users can access the field
list and create their pivot any way they choose (the point is to get
them the data). Works like a charm.
The problem: When a number field is dragged into the Data Area, the
decimals are not there. When checking the Field Settings, the format
is set to General. Changing the format to Number with 2 decimal places
changes the value to nnn.00 - the decimal values are missing.
Naturally this will cause some excitement as the amounts are not
accurate, expecially at the summary level.
Note: When I create a pivot using the pivot table wizard from the
menu, using the same SQL string to collect the data from Oracle, the
numbers in the data area have a General format but the decimals are
present (and visible). So the SQL is good. Looking at the two sets of
pivots, the decimals in the automated table are simply truncated, the
numbers have not been rounded. The text fields in the automated table
are behaving as expected.
The question: Is there a setting/property I can use to preserve the
decimal values? What am I missing? They can be hidden or visible, but
need to be there.
Here, in part, is the addin's automated code:
[I am passing a recordset (empty) to a function that pulls the data
from Oracle using a passed SQL string. The recordset is populated with
the data, and the pivot table is created using the recordset as the
source.]
Public sub s_CreatePivot
Dim rstADO As ADODB.Recordset
Dim objPivotCache As PivotCache
strSQLCall = "select * from schema.table_name"
'Get a recordset object.
Set rstADO = New ADODB.Recordset
'Open the recordset object with the passed SQL string
If f_FetchReportData(rstADO, strSQLCall) = True Then
'Assign the recordset to the NEW pivot cache and create a pivot table.
Set objPivotCache =
ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
Set objPivotCache.Recordset = rstADO
With objPivotCache
..CreatePivotTable TableDestination:=Range("A4"), TableName:="MyPivot"
End With
End If
'Clean up
rstADO.Close
Set rstADO = Nothing
Exit Sub
I appreciate your time and suggestions.
Many thanks,
Misty