I am calling Excel to hold a series of database data and then create a pivot
table from it (VB from Visual studio 2008 using office interop). Works fine
on my development and test boxes (both have Office 2007). The target system
has Office 2003 and fails on the pivot table wizard.

'Create a Pivot Table - CAUSES an ERROR with Excel 2003 !
xlSheet2 = xlBook.Sheets("Sheet2")
xlSheet2.Name = "PivotTable"

Dim pivotTable As PivotTable = Nothing
Dim CourseList As PivotField = Nothing
Dim NatList As PivotField = Nothing
Dim NatSum As PivotField = Nothing
Dim pivotTableName As String = "Nationality Analsys"

' Add a pivot table to the worksheet.
Dim EndCell As String = "C" + RowNo.ToString
xlBook.PivotTableWizard(XlPivotTableSourceType.xlDatabase, _
xlSheet.Range("A1", EndCell), xlSheet2.Range("A1"),
pivotTableName, True, True, _
True, True, , , False, False, XlOrder.xlDownThenOver, 0)

' Set variables for used to manipulate the Pivot Table.
pivotTable = xlSheet2.PivotTables(pivotTableName)
CourseList = pivotTable.PivotFields(1)
NatList = pivotTable.PivotFields(2)
NatSum = pivotTable.PivotFields(3)

Format the Pivot Table.
'pivotTable.Format(XlPivotFormatType.xlReport2)
pivotTable.InGridDropZones = False

' Set a Row Field.
CourseList.Orientation = XlPivotFieldOrientation.xlColumnField
NatList.Orientation = XlPivotFieldOrientation.xlRowField
NatSum.Orientation = XlPivotFieldOrientation.xlDataField

' Set a Value Field.
NatSum.Orientation = XlPivotFieldOrientation.xlDataField
NatSum.Function = XlConsolidationFunction.xlSum


-------

So it seems that Excel 2003 does not have the pivotwizard. The question is
what can I use instead and what would work on both Office 2003 and 2008?

---

Don C

Yes, that feature was added in Excel 2007. Unfortunately, when developing Office automation apps you need to restrict functionality to the minimum supported version.

You could also try posting to the VBA forum to see ifanyone over there has anyideas with respect to a workaround to support the functionality.

http://social.msdn.microsoft.com/forums/en-US/isvvba/threads/

---

Paul ~~~~ Microsoft MVP (Visual Basic)

You probably need to indicate what type of error you are encountering. AFAIK, the PivotTableWizard method has been supported in the last few versions.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

OOPS, I meant to put it in.

Public member "InGridDropZones" not found on type 'Pivot table' not found.

All the Office 2003 systems I have tried it on have failed. All Office 2007 work fine.

---

Don C

Yes, that feature was added in Excel 2007. Unfortunately, when developing Office automation apps you need to restrict functionality to the minimum supported version.

You could also try posting to the VBA forum to see ifanyone over there has anyideas with respect to a workaround to support the functionality.

http://social.msdn.microsoft.com/forums/en-US/isvvba/threads/

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Thanks for the feedback.

---

Don C