PivotCaches.Add fails in Excel 2000

[MrExcel]

This is driving me crazy. This line of code is working in Excel 2002, and failing in Excel 2000.

Dim PTCache as PivotCache
Set MyRange = Range("StyleCache")
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDataBase, SourceData:=MyRange)

It is failing with Run Time Error '5': Invalid Procedure Call or Argument.

What the heck?
There are only 2 arguments.
xlDataBase has a value of 1 in both systems.
MyRange is valid, has 3 columns, with headings of Store, Style, "Sales 7", and 330 rows of data.

Any ideas?

Bill

 

[smozgur]

Hi Bill,

Did you try this?

Dim PTCache As PivotCache
Set MyRange = Range("A1:B10")
Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, MyRange)

Suat

 

[Mudface]

The problem seems to be with your Range variable, as the following works OK, with the myRange commented out: -

Dim PTCache As PivotCache
'Set myrange = Range("stylecache")
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="=stylecache")

No idea why this works with Excel 2002 and not 2000 though.

Edit: Sorry, missed out an '=' above.
This message was edited by Mudface on 2002-05-04 12:30

 

[Mudface]

The following should work as well: -

Dim PTCache As PivotCache
Dim myRange As String
myRange = Sheet1.Range("StyleCache").Address
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRange)
This message was edited by Mudface on 2002-05-04 12:42

 

[MrExcel]

Suat & Mudface: This board is a fantastic resource. Thanks for the help. Mudface - I tried the "=stylecache" first and it worked wonderfully.


Bill

_________________
MrExcel.com Consulting
This message was edited by MrExcel on 2002-05-04 14:04

 

[GeorgeB]

Just a thought from the peanut gallery.
If your source data is not a DYNAMIC RANGE
then I urge you to get familiar with this concept. Check my last post on page 5 or maybe 6. Aladin stepped me through it and it is great stuff.

 

[Mudface]

Is this a bug or a mistake in the documentation, do you think? The SourceData arguement is specified as an optional or required variant, but seems to be a required string in Excel 2000. Has this changed for 2002?

 

[MrExcel]

They probably just "eased up" the requirements in Excel 2002. Change is good - it just makes cross platform deployment a little more crazy.