Decoupling the source of a Chart

[snigdha]

Hi,
I have come across a wierd problem. I have a macro in excel which generates charts after reading from various excel sheets. So i am running a loop to read the excel file name and then generate the chart.

To generate the chart, I set the source of the chart to the read excel file like this:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F1:G1486"), PlotBy _
:=xlColumns

Now, this works correct. But now, in the same range of sheet1, i copy some other data and try to genarate a chart from the new data. But because i have set the source of the previous chart to the same range, that chart changes as well.

So is there some way to stop that chart from changing or decoupling the chart from that range of data. Because i dont want to create a new data sheet for every chart i make.

Please do let me know,
Thanks,
Snigdha

[Tom Ogilvy]

do copy as picture and paste a picture of the chart.

then you don't have to build a new chart at all, but just use the existing
chart to reflect the new data, then copy as picture and paste and keep
repeating until you have all the charts you need.

These charts are now pictures, however, and can not be manipulated as
charts.

--
Regards,
Tom Ogilvy

"snigdha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have come across a wierd problem. I have a macro in excel which
> generates charts after reading from various excel sheets. So i am
> running a loop to read the excel file name and then generate the chart.
>
>
> To generate the chart, I set the source of the chart to the read excel
> file like this:
>
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F1:G1486"),
> PlotBy _
> :=xlColumns
>
> Now, this works correct. But now, in the same range of sheet1, i copy
> some other data and try to genarate a chart from the new data. But
> because i have set the source of the previous chart to the same range,
> that chart changes as well.
>
> So is there some way to stop that chart from changing or decoupling the
> chart from that range of data. Because i dont want to create a new data
> sheet for every chart i make.
>
> Please do let me know,
> Thanks,
> Snigdha
>
>
> --
> snigdha
> ------------------------------------------------------------------------
> snigdha's Profile:
http://www.excelforum.com/member.php...o&userid=27370
> View this thread: http://www.excelforum.com/showthread...hreadid=468844
>

[Peter T]

Hi Snigdha,

Tom's suggestion is the simplest approach. But if you want to retain the
chart and data maybe you can move the source data of the old chart, eg
insert 2 columns in F:G, or entirely elsewhere and reset the source data of
the old chart. In both cases before copying data into the old source range
and creating the new chart.

If that's not viable, with small numbers of series values you can convert
series formula(s) from source to actual values. But if you have 1485/6
values that won't be possible because you will exceed the max 1024 string
length for a series formula.

If your only option is to totally "decouple from source" I have something
that delinks virtually everything in a chart from cells and caters for very
large volumes of X & Y values.

Regards,
Peter T
pmbthornton gmail com

"snigdha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> I have come across a wierd problem. I have a macro in excel which
> generates charts after reading from various excel sheets. So i am
> running a loop to read the excel file name and then generate the chart.
>
>
> To generate the chart, I set the source of the chart to the read excel
> file like this:
>
> ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F1:G1486"),
> PlotBy _
> :=xlColumns
>
> Now, this works correct. But now, in the same range of sheet1, i copy
> some other data and try to genarate a chart from the new data. But
> because i have set the source of the previous chart to the same range,
> that chart changes as well.
>
> So is there some way to stop that chart from changing or decoupling the
> chart from that range of data. Because i dont want to create a new data
> sheet for every chart i make.
>
> Please do let me know,
> Thanks,
> Snigdha
>
>
> --
> snigdha
> ------------------------------------------------------------------------
> snigdha's Profile:
http://www.excelforum.com/member.php...o&userid=27370
> View this thread: http://www.excelforum.com/showthread...hreadid=468844
>

[snigdha]

Hi,
I am pursuing the way of copying and pasting as a picture becuse that seems to be easy as serves my purpose well. But am thoroughly confused.

Basically I do a Chart.Add and then set its source and draw the chart.
Now i want to copy this chart and paste it. But when i copy and paste it. it overlaps the original chart and then i dont knw how to delete the original one.
Because if i delete the original one, that entire chart gets deleted.

Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("F1:G1486"), PlotBy :=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet

ActiveChart.CopyPicture xlScreen, xlPicture
ActiveChart.Paste

Please do let me know that how do i get rid of the origical chart from here.

Thanks a lot for the help,
Snigdha

[snigdha]

Hi,
What i finally did was..

ActiveChart.CopyPicture xlScreen, xlPicture
Sheets.Add after:=Worksheets("Sheet1")
Sheets("Sheet7").Paste
ActiveChart.delete

Now, the prblem is that I want to set the cells of the new sheets to none. so that in the background the gridlines cannot be seen. Can you let me know how do I do it and if this approach is alright?

Once again,
Thanks a lot,
Snigdha

[Peter T]

Just for ideas have a go with this.

Put some number is A1:A3, hopefully you will end up with 5 chart pictures in
a row.

Sub test()
Dim i As Long
Dim chtLeft As Single, chtTop As Single
Dim cht As Chart
Dim ws As Worksheet
Dim pic As Picture

Application.ScreenUpdating = False
Set ws = ActiveSheet
For i = 1 To 5
If i = 1 Then
Set cht = ws.ChartObjects.Add(0, 0, 160, 100).Chart
End If
With cht
.SetSourceData _
Source:=Sheets("Sheet1").Range("a1:a3"), PlotBy:=xlColumns
cht.HasTitle = True
.ChartTitle.Text = "MyChart " & i
.CopyPicture xlScreen, xlPicture
End With
ws.Paste
Next
cht.Parent.Activate
cht.Parent.Delete

chtLeft = 10: chtTop = 10
For Each pic In ActiveSheet.Pictures
With pic
.Left = chtLeft
.Top = chtTop
.Height = 100
.Width = 160
chtLeft = chtLeft + .Width + 10
End With
Next
Application.ScreenUpdating = True
ws.Activate
ActiveWindow.DisplayGridlines = False
End Sub

Regards,
Peter T


"snigdha" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
> What i finally did was..
>
> ActiveChart.CopyPicture xlScreen, xlPicture
> Sheets.Add after:=Worksheets("Sheet1")
> Sheets("Sheet7").Paste
> ActiveChart.delete
>
> Now, the prblem is that I want to set the cells of the new sheets to
> none. so that in the background the gridlines cannot be seen. Can you
> let me know how do I do it and if this approach is alright?
>
> Once again,
> Thanks a lot,
> Snigdha
>
>
> --
> snigdha
> ------------------------------------------------------------------------
> snigdha's Profile:
http://www.excelforum.com/member.php...o&userid=27370
> View this thread: http://www.excelforum.com/showthread...hreadid=468844
>

[snigdha]

Hey,
That was really nice sample...
Thanks a lot for it, I think I got it all right now.

Everything has been explained, thanks for the spoon feeding

Snigdha