Prev: Button to add Rows on Form in Excel
Next: Controls in a frame within a frame are always invisible - What am I doing wrong
From: Catenary on 24 May 2010 11:17
This macro worked in Excel2003 but fails in 2007. It was designed to create
a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
concentrations of 150 chemical parameters in 8 wells over 28 sample periods.

Instead of creating the chart with 150 series lines on it, I had it show
only 3 by repeating the line

..PivotItems("2-Butanone").Visible = False

147 times with a different chemical in quotes each time.

The problem seems to be that Excel 2007 can only handle 119 repetitions of
this command because every time the macro is executed it results in

“Run-time error '-2147417848 (80010108)'
Method 'visible' of object 'PivotItem' failed”

I have two questions:

1) Is there a more elegant way to create a pivot chart that has the ability
to show any (or all) of the 150 chemicals but is initially drawn with only a
few?
2) Has anyone else found that Excel 2007 is generally slower, less robust
and more prone to problems than Excel 2003?

Thank you for any help you can provide. If you think code for the entire
might help, I'll be glad to provide it.

From: Barb Reinhardt on 24 May 2010 11:37
IIRC, you need to have at least one pivot item visible or it gives this error
(haven't tested it today). Might that be the issue?
--
HTH,

Barb Reinhardt



"Catenary" wrote:

> This macro worked in Excel2003 but fails in 2007. It was designed to create
> a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
> concentrations of 150 chemical parameters in 8 wells over 28 sample periods.
>
> Instead of creating the chart with 150 series lines on it, I had it show
> only 3 by repeating the line
>
> .PivotItems("2-Butanone").Visible = False
>
> 147 times with a different chemical in quotes each time.
>
> The problem seems to be that Excel 2007 can only handle 119 repetitions of
> this command because every time the macro is executed it results in
>
> “Run-time error '-2147417848 (80010108)'
> Method 'visible' of object 'PivotItem' failed”
>
> I have two questions:
>
> 1) Is there a more elegant way to create a pivot chart that has the ability
> to show any (or all) of the 150 chemicals but is initially drawn with only a
> few?
> 2) Has anyone else found that Excel 2007 is generally slower, less robust
> and more prone to problems than Excel 2003?
>
> Thank you for any help you can provide. If you think code for the entire
> might help, I'll be glad to provide it.
>
From: Roger Govier on 24 May 2010 12:55
Hi

Send me a copy of the workbook, and I'll take a look.
To mail direct, send to
roger at technology4u dot co dot uk
Remove the at and dots to make a valid email address.
--
Regards
Roger Govier

Catenary wrote:
> This macro worked in Excel2003 but fails in 2007. It was designed to create
> a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
> concentrations of 150 chemical parameters in 8 wells over 28 sample periods.
>
> Instead of creating the chart with 150 series lines on it, I had it show
> only 3 by repeating the line
>
> .PivotItems("2-Butanone").Visible = False
>
> 147 times with a different chemical in quotes each time.
>
> The problem seems to be that Excel 2007 can only handle 119 repetitions of
> this command because every time the macro is executed it results in
>
> “Run-time error '-2147417848 (80010108)'
> Method 'visible' of object 'PivotItem' failed”
>
> I have two questions:
>
> 1) Is there a more elegant way to create a pivot chart that has the ability
> to show any (or all) of the 150 chemicals but is initially drawn with only a
> few?
> 2) Has anyone else found that Excel 2007 is generally slower, less robust
> and more prone to problems than Excel 2003?
>
> Thank you for any help you can provide. If you think code for the entire
> might help, I'll be glad to provide it.
>
From: Catenary on 27 May 2010 15:53
I have left three series displayed, so that shouldn't be the problem.

"Barb Reinhardt" wrote:

> IIRC, you need to have at least one pivot item visible or it gives this error
> (haven't tested it today). Might that be the issue?
> --
> HTH,
>
> Barb Reinhardt
>
>
>
> "Catenary" wrote:
>
> > This macro worked in Excel2003 but fails in 2007. It was designed to create
> > a pivot chart from a large (12,500 rows x 14 columns) spreadsheet listing the
> > concentrations of 150 chemical parameters in 8 wells over 28 sample periods.
> >
> > Instead of creating the chart with 150 series lines on it, I had it show
> > only 3 by repeating the line
> >
> > .PivotItems("2-Butanone").Visible = False
> >
> > 147 times with a different chemical in quotes each time.
> >
> > The problem seems to be that Excel 2007 can only handle 119 repetitions of
> > this command because every time the macro is executed it results in
> >
> > “Run-time error '-2147417848 (80010108)'
> > Method 'visible' of object 'PivotItem' failed”
> >
> > I have two questions:
> >
> > 1) Is there a more elegant way to create a pivot chart that has the ability
> > to show any (or all) of the 150 chemicals but is initially drawn with only a
> > few?
> > 2) Has anyone else found that Excel 2007 is generally slower, less robust
> > and more prone to problems than Excel 2003?
> >
> > Thank you for any help you can provide. If you think code for the entire
> > might help, I'll be glad to provide it.
> >
 | 
Pages: 1
Prev: Button to add Rows on Form in Excel
Next: Controls in a frame within a frame are always invisible - What am I doing wrong