Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: PivotCaches.Add fails in Excel 2000

  1. #1
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    865
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    View a collection of recent Excel articles in the Excel Daily News

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    816
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bill,

    Did you try this?

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

    Suat
    Suat

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    865
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Las Vegas Nevada USA
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    George

    Learn to listen. Opportunity sometimes knocks very softly.

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  8. #8
    . MrExcel's Avatar
    Join Date
    Feb 2002
    Location
    Merritt Island Florida
    Posts
    865
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default

    They probably just "eased up" the requirements in Excel 2002. Change is good - it just makes cross platform deployment a little more crazy.
    View a collection of recent Excel articles in the Excel Daily News

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •