?
Solved

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

Posted on 2003-03-04
11
Medium Priority
?
690 Views
Last Modified: 2012-08-13
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
Comment
Question by:fred24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8065064
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
 

Author Comment

by:fred24
ID: 8065159
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8065263
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fred24
ID: 8065353
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8065987
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
 

Author Comment

by:fred24
ID: 8070220
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
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8070396
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
 

Author Comment

by:fred24
ID: 8070452
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
 
LVL 9

Accepted Solution

by:
pauloaguia earned 150 total points
ID: 8070661
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
 

Author Comment

by:fred24
ID: 8070889
Activating the worksheet and chartobject is essential. No need to activate the chart.

0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8071548
Right, I took the wrong variable...

Glad you could work it out.
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question