Hi everyone!

I have a workbook in Excel 2003 with 2 worksheets with some ~6000 rows
each. Now I want to add some charts. On the first one, this seems to
work fine, and I set a bunch of properties (including the data source)
manually. Then when I add the 2nd chart, it predraws the whole thing
with all 6000 lines of data, even though I haven't yet defined any data
source. It just takes some default source and paints it. Of course this
is *very* annoying and unnecessarily slowing the whole thing down. Is
there any way to configure Excel so that a new chart will be a "naked"
chart, without attempting to display *any* data until I manually set up
a data source?

Grateful for any tips!

Lars

Apparently, when you create a new chart, this useless Excel copies the
data source settings from the last chart you created. So the workaround
(when you start with zero charts) is: Create as many charts as you need
(empty) before doing anything to them.

*Then* fill them with data and set their attributes as you would like to
have them.

Very annoying and very very poor thinking on M$ side. I am amazed at how
they could possibly create such a shitty software when they have done
such a powerful and infinitely more useful tool as Access.

And no, I am not voluntarily using Excel, I am in the state of migrating
someone else's application to Access or something better.

Best Regards,

Lars

Hi,

You could try making sure the active cell is not within your data set.

Cheers
Andy

Lars Uffmann wrote:
> Hi everyone!
>
> I have a workbook in Excel 2003 with 2 worksheets with some ~6000 rows
> each. Now I want to add some charts. On the first one, this seems to
> work fine, and I set a bunch of properties (including the data source)
> manually. Then when I add the 2nd chart, it predraws the whole thing
> with all 6000 lines of data, even though I haven't yet defined any data
> source. It just takes some default source and paints it. Of course this
> is *very* annoying and unnecessarily slowing the whole thing down. Is
> there any way to configure Excel so that a new chart will be a "naked"
> chart, without attempting to display *any* data until I manually set up
> a data source?
>
> Grateful for any tips!
>
> Lars

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy,

Andy Pope wrote:
> You could try making sure the active cell is not within your data set.
Thank you, that did help me to understand where Excel is getting it's
data from. And it works. The "active cell" after I add and manipulate a
chart is the complete source data range of the current chart, so that's
why when adding a new chart then, I get this chart full of unwanted data.

Sadly, your tip does not solve my problem, since I want to do everything
in the background, i.e. I want to completely avoid that the current
selection or the current focus have any influence on the behaviour of my
VBA script. This is so that the user doesn't mess up the script with an
accidental click on some cell/sheet in the middle of processing - and I
do want to allow the user to maybe continue working on a different Excel
sheet. I'll just go with my workaround unless something better comes up.

Thank you!

Lars

Hi,

You did mention you where using code. Try this to create a blank chart
sheet.

Sub x()

Dim chtTemp As Chart

Set chtTemp = Sheet1.ChartObjects.Add(1, 1, 200, 200).Chart
chtTemp.Location Where:=xlLocationAsNewSheet

End Sub

Cheers
Andy

Lars Uffmann wrote:
> Hi Andy,
>
> Andy Pope wrote:
>
>> You could try making sure the active cell is not within your data set.
>
> Thank you, that did help me to understand where Excel is getting it's
> data from. And it works. The "active cell" after I add and manipulate a
> chart is the complete source data range of the current chart, so that's
> why when adding a new chart then, I get this chart full of unwanted data.
>
> Sadly, your tip does not solve my problem, since I want to do everything
> in the background, i.e. I want to completely avoid that the current
> selection or the current focus have any influence on the behaviour of my
> VBA script. This is so that the user doesn't mess up the script with an
> accidental click on some cell/sheet in the middle of processing - and I
> do want to allow the user to maybe continue working on a different Excel
> sheet. I'll just go with my workaround unless something better comes up.
>
> Thank you!
>
> Lars

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Hi Andy!

Andy Pope wrote:
> You did mention you where using code. Try this to create a blank chart
> sheet.
> Set chtTemp = Sheet1.ChartObjects.Add(1, 1, 200, 200).Chart
> chtTemp.Location Where:=xlLocationAsNewSheet


Thanks a lot! This is 95% of the solution I was looking for, and
probably the best Excel can do
Unfortunately, the Location method creates a copy of the chart object
and renders the old chart object returned by Add().Chart
obsolete/invalid. Also, the documentation does not say where the
"xlLocationAsNewSheet" inserts the chart. I *assume* it will always be
at Workbook.Charts(Workbook.Charts.Count + 1) and of course increase the
Charts Count. So I have to get a new handle to my chart by accessing
Workbook.Charts(Workbook.Charts.Count) after moving the chart.

What I did in the end was this:

Dim dummyWs As WorkSheet
Dim myChart As Chart

Set dummyWs = ActiveWorkbook.Worksheets.Add

dummyWs.ChartObjects.Add(1, 50, 100, 50).Chart.Location
xlLocationAsNewSheet, "my chart in a new sheet"
Set myChart = ActiveWorkbook.Charts(ActiveWorkbook.Charts.Count)

' do stuff with myChart

This gets the job done, so thank you very much!

Best Regards,

Lars

My standard approach to losing the reference after changing the
location is

set chtTemp=chtTemp.Location(Where:=xlLocationAsNewSheet)

In your case, what I would do is just use the Charts.Add method. Also,
I have learnt not to trust Excel's decisions about what it puts in a
new chart. So, I always go through and delete any existing series.

Option Explicit

Sub doChart()
Dim aChart As Chart
Set aChart = Charts.Add()
With aChart.SeriesCollection
Do While .Count > 0: .Item(1).Delete: Loop
End With
End Sub



On Thu, 24 Jul 2008 15:30:25 +0200, Lars Uffmann <(E-Mail Removed)>
wrote:

>Hi Andy!
>
>Andy Pope wrote:
>> You did mention you where using code. Try this to create a blank chart
>> sheet.
>> Set chtTemp = Sheet1.ChartObjects.Add(1, 1, 200, 200).Chart
>> chtTemp.Location Where:=xlLocationAsNewSheet
>
>
>Thanks a lot! This is 95% of the solution I was looking for, and
>probably the best Excel can do
>Unfortunately, the Location method creates a copy of the chart object
>and renders the old chart object returned by Add().Chart
>obsolete/invalid. Also, the documentation does not say where the
>"xlLocationAsNewSheet" inserts the chart. I *assume* it will always be
>at Workbook.Charts(Workbook.Charts.Count + 1) and of course increase the
>Charts Count. So I have to get a new handle to my chart by accessing
>Workbook.Charts(Workbook.Charts.Count) after moving the chart.
>
>What I did in the end was this:
>
> Dim dummyWs As WorkSheet
> Dim myChart As Chart
>
> Set dummyWs = ActiveWorkbook.Worksheets.Add
>
> dummyWs.ChartObjects.Add(1, 50, 100, 50).Chart.Location
>xlLocationAsNewSheet, "my chart in a new sheet"
> Set myChart = ActiveWorkbook.Charts(ActiveWorkbook.Charts.Count)
>
> ' do stuff with myChart
>
>This gets the job done, so thank you very much!
>
>Best Regards,
>
> Lars

Tushar,

Tushar Mehta (Microsoft MVP Excel 2000-2008) wrote:
> My standard approach to losing the reference after changing the
> location is
> set chtTemp=chtTemp.Location(Where:=xlLocationAsNewSheet)

I might try again, but I tried this and I could swear that for the Chart
object, .Location did not return a new reference to a Chart object -
sadly.

> In your case, what I would do is just use the Charts.Add method. Also,
> I have learnt not to trust Excel's decisions about what it puts in a
> new chart. So, I always go through and delete any existing series.

You kinda missed the beginning of the discussion I decided to
explicitely *not* use the Charts.Add method because of what Excel puts
in that new chart. Andy's solution actually enables you to create a
*really* empty chart (without even a Series in it).

Thank you for your additions though!

Best Regards,

Lars

Lars,

I've been using the Location method to get a reference to the new
object for several versions of Excel including 2007. Not to mention,
as is almost always the case, I tested the code before posting. {grin}

On Thu, 24 Jul 2008 19:19:43 +0200, Lars Uffmann <(E-Mail Removed)>
wrote:

>Tushar,
>
>Tushar Mehta (Microsoft MVP Excel 2000-2008) wrote:
>> My standard approach to losing the reference after changing the
>> location is
>> set chtTemp=chtTemp.Location(Where:=xlLocationAsNewSheet)
>
>I might try again, but I tried this and I could swear that for the Chart
> object, .Location did not return a new reference to a Chart object -
>sadly.
>
>> In your case, what I would do is just use the Charts.Add method. Also,
>> I have learnt not to trust Excel's decisions about what it puts in a
>> new chart. So, I always go through and delete any existing series.
>
>You kinda missed the beginning of the discussion I decided to
>explicitely *not* use the Charts.Add method because of what Excel puts
>in that new chart. Andy's solution actually enables you to create a
>*really* empty chart (without even a Series in it).
>
>Thank you for your additions though!
>
>Best Regards,
>
> Lars

Good morning Tushar,

Tushar Mehta (Microsoft MVP Excel 2000-2008) wrote:
> I've been using the Location method to get a reference to the new
> object for several versions of Excel including 2007. Not to mention,
> as is almost always the case, I tested the code before posting. {grin}

You were right. I have no idea what I was trying before that did not
work, but the Location object does indeed return the correct reference
to the new chart object, as you said. I can do without my previous
workaround now, thanks a lot!

Best Regards,

Lars