Decoupling the source of a Chart
 

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

Decoupling the source of a Chart
 

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" wrote in
message ...

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

Decoupling the source of a Chart
 

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" wrote in
message ...

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

Decoupling the source of a Chart
 

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 Whe=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
------------------------------------------------------------------------
snigdha's Profile: http://www.excelforum.com/member.php...o&userid=27370
View this thread: http://www.excelforum.com/showthread...hreadid=468844

Decoupling the source of a Chart
 

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

Decoupling the source of a Chart
 

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" wrote in
message ...

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

Decoupling the source of a Chart
 

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


--
snigdha
------------------------------------------------------------------------
snigdha's Profile: http://www.excelforum.com/member.php...o&userid=27370
View this thread: http://www.excelforum.com/showthread...hreadid=468844