Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error deleting pivotitems (2000 2002 2003)

    I am trying to write a utility which programmatically clears out all the pivotitems in a pivottable. Essentially, it works like this:

    for each pvtitem in pvtfield.pvtitems
    pvtitem.delete
    next

    Often, this works, but sometimes the delete action raises an error "1004: Application-defined or object-defined error". What causes that, and is there a way around it?

    Thanks...

    Dan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error deleting pivotitems (2000 2002 2003)

    When deleting items from a collection, it's safest to loop backwards:

    Dim i As Integer
    For i = pvtfield.PivotItems.Count To 1 Step -1
    pvtfield.PivotItems(i).Delete
    Next i

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error deleting pivotitems (2000 2002 2003)

    Thanks Hans. However, after making the suggested changes, I still get the same behavior. It appears that, if the pivotitem has underlying data associated with it, Excel won't let me delete it. Does that sound right?

    Dan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error deleting pivotitems (2000 2002 2003)

    Why would you want to delete a pivot item with data in it? If you only want to remove unused pivot items, you can put

    On Error Resume Next

    before the loop. Don't forget to restore normal error handling after it.

  5. #5
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error deleting pivotitems (2000 2002 2003)

    Thanks Hans. That's what I was doing to get around this. My boss--for reasons not quite clear to me--wants to be able to clean out these pivot tables so that we can start completely from scratch with only the fields and table layout defined.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error deleting pivotitems (2000 2002 2003)

    If you really want to start from scratch, it's probably "cleaner" to delete the pivot table and create a new one. This can be done in code.

Posting Permissions

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