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
