Issue with PIVOT table creation using VBA

[Pooja Nair]

I have a requirement in which i have to create a pivot table. I created it in Excel 2007 and tried running in a system having both Excel 2003 and Excel 2007 installed. It works fine till 4-5 columns are added. But after that it is crashing saying "AddDataField method of PivotTable class failed". It is working fine in machines having only Excel 2007 installed.

Tried running in machines having 1 gb, 2 gb (RAM) etc. In all the cases, it is working fine in machines having only 2007 installed but fails in machines having 2003 and 2007 Excel.

Could anyone help me out with this issue?

 

[jakeman]

I have a requirement in which i have to create a pivot table. I created it in Excel 2007 and tried running in a system having both Excel 2003 and Excel 2007 installed. It works fine till 4-5 columns are added. But after that it is crashing saying "AddDataField method of PivotTable class failed". It is working fine in machines having only Excel 2007 installed.

Tried running in machines having 1 gb, 2 gb (RAM) etc. In all the cases, it is working fine in machines having only 2007 installed but fails in machines having 2003 and 2007 Excel.

Could anyone help me out with this issue?

Pooja - I'm not an expert but I code enough to know a thing or two.

I have run into problems coding back and forth between Excel 2003 and 2007. Some changes were made to syntax in 2007 that are not supported in 2003...I found that out with the Autofilter code with multiple criteria. For example, the following code is what I created in Excel 2007:

Range("A6:N6").autofilter Field:=1, Criteria1:=">=" & bDate, Criteria2:="<=" & eDate

When I ran my macro it worked fine but when I ran it in Excel 2003 it didn't work. I studied the code and realized that in 2003, the same thing that I was trying to accomplish was a little more lengthy:

Range("A6:N6").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=" & bDate, Criteria2:="<=" & eDate

Excel 2007 shortened the need to spell things out, making it much easier to accomplish the same goal. I may be wrong but you might want to look at the nuances between 2003 and 2007 for what you are trying to do...the fact that your code works fine on machines that are installed only with 2007 makes me think it is an issue of code variations.

Jake