Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 699
  • Last Modified:

remove series from pivotchart - error 1004 (delete method of series class failed) - Excel 97

I have an embedded pivot chart that plots from a pivot table containing 2 data fields. The chart only plots one of the data fields - the other is programmatically removed (see code below).

Sub FormatPivotTables()

Dim co As ChartObject
Dim c As Chart
Dim sc As SeriesCollection
Dim s As Series
Dim mysheet As Worksheet
Dim wkbNewBook As Excel.Workbook

On Error GoTo FormatPivotTable_error

Set wkbNewBook = ActiveWorkbook

wkbNewBook.RefreshAll

With wkbNewBook
   For Each wksSheet In .Worksheets
       If InStr(wksSheet.Name, "#") Then
       
          Set mysheet = Sheets(wksSheet.Name)
          Set co = mysheet.ChartObjects(1)
          Set c = co.Chart
          Set sc = c.SeriesCollection

          For Each s In sc
              If LCase(InStr(s.Name, "% of total")) Or LCase(InStr(s.Name, "Total Number")) Or LCase(InStr(s.Name, "Grand Total")) Then
                 sc.Item(s.Name).Delete
              End If
          Next
       End If
   Next wksSheet
End With

Exit Sub

FormatPivotTable_error:
    ErrorOutput = sc.Item(s.Name).Name
    MsgBox "Procedure: FormatPivotTable" & Chr(13) & "Status: deleting " & sc.Item(s.Name).Name & Chr(13) & "Description: " & Err.Description, vbOKOnly, "Call Enquiry Analysis"
    Err.Clear

End Sub

The problem I have is whenever the pivot is updated and the program run, I get an error 1004 on the following line of code 'sc.Item(s.Name).Delete'.

If I manually remove the series and then run the code again it is fine.

Whatever I try I can't get it to work.
0
fred24
Asked:
fred24
  • 6
  • 5
1 Solution
 
pauloaguiaCommented:
That If statement is looking a little suspicious to me. What are you trying to check?

Shouldn't it read something like

If (InStr(LCase(s.Name), "% of total") > 0) Or (InStr(LCase(s.Name), "total number") > 0) Or (InStr(LCase(s.Name), "grand total") > 0) Then

?

Just a thought. I don't have XL2000 here right now so I can't test anything related to Pivot Charts. But I'll try it later.


Another thing: if your code gives you an error then stop it and imediately start recording a macro and finish the action manually. Then take a close look at the code. It's not the best code in the world but sometimes many things we learn to take out like an Activate or something make all the difference in the world.

Hope this helps

Paulo
0
 
fred24Author Commented:
Thanks, Paulo. The line you mention is checking whether the fields in the pivot table can be deleted - I only want to delete series that contain the string 'Grand Total' or '% of total' or 'Total Number'.

BTW - this may not be a problem in XL2000 - I'm running 97.
0
 
pauloaguiaCommented:
Sorry. I was missguided by the mixing of Pivot Tables and Charts. In XL2K you can have them both in the same object.

Another thing I noticed:

Why not just change the code like this:

...
         For Each s In sc
             If ... Then
                s.Delete
             End If
         Next
...

Seems to do the same and it's simpler...

Also take a look at http://support.microsoft.com/default.aspx?scid=KB;en-us;q168650 It addresses the error you are reporting.

Hope this helps

Paulo
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
fred24Author Commented:
Hi Paulo - Thanks for the tip. I tried the code change and it still errors.

Re: the knowledge document, I not sure it is related but I turned off 'auto scale' anyway. Sorry to report that the problem didn't go away.
0
 
pauloaguiaCommented:
I'm sorry I'm mixing things already. I confused this one with another question I was handling where memory was an issue.

What is the exact error message you're getting?

Also, have you tried removing that series manually and recording a macro in the process?

Paulo
0
 
fred24Author Commented:
Hi - full error is: error 1004 (delete method of series class failed).

Yes I did remove the series manually and it worked fine after that. However, when the pivot is updated with new values the pivot table is updated, and the program errors again.

I did record the same process manually and took a look at the macro but it was very different code and specific to the chart and series. The code I have is generic to all charts and all series.

Thanks for your help.
0
 
pauloaguiaCommented:
Paste the generated code here anyway. Maybe it will give us a hint on something you must do for your own code to work. Since the generated code works anyway...

It's a long shot but quite frankly I don't see why the series deletion could be failing...

Paulo
0
 
fred24Author Commented:
So the following macro code selects a worksheet, then select the embedded chart, and the deletes the first series.

    Sheets("#Business Area#").Select
    ActiveSheet.ChartObjects("Chart 4").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(2).Delete

Many thanks.
0
 
pauloaguiaCommented:
Taking a look at some of my own code for deleting series I noticed that I allways activate the chartObject first. Maybe it's not a coincidence.
So maybe add this line somewhere before the For... cycle:

c.Activate

Also is your chart a chart sheet or an embeded chart? That may also have something to do with it. in either case if you have an error activating the chart maybe you need also to activate it's sheet first.

Paulo

0
 
fred24Author Commented:
Activating the worksheet and chartobject is essential. No need to activate the chart.

0
 
pauloaguiaCommented:
Right, I took the wrong variable...

Glad you could work it out.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now