Linking Chart

[Macroman]

Excel 2000

I have a chart that is created in book1.xls I would like an embedded object
in book2.xls
that is linked to the chart in book1.xls.

in previous version of Excel I would copy the chart from book1.xls then goto
book2.xls
select Paste Special , then Microsoft Chart Object and put a check against
paste as link,
much like what can be done if pasting into word or powerpoint.

The reason I want this in Book2.xls is that book2 will contain a report and
I do not
want the chart data in this workbook. I will create a macro in book2 to
break the chart links
and turn the chart into a picture. Book2.xls will be my master template and
will
be updated from month to month.

thanks

Macroman

 

[Jon Peltier]

If you paste the chart into another workbook, it does not paste all the
data into the new workbook. It pastes a fully functional, formattable
chart, that links back to the data in the original workbook. Someone
could hack the values from the chart, but they won't have access to all
the data in the first workbook.

If you don't need to edit the chart while writing the report, then by
all means follow Everett's process, which is, in fact, the way I paste
charts that I don't want other people monkeying with.

Here's something that will turn all your charts into pictures (run this
in the new workbook, not the original):

Sub ChartsToPictures()
Dim wks As Worksheet
Dim chtob As ChartObject
Dim pic As Picture
For Each wks In ThisWorkbook.Worksheets
For Each chtob In wks.ChartObjects
chtob.Chart.CopyPicture Appearance:=xlScreen, _
Format:=xlPicture, Size:=xlScreen
wks.Paste
Set pic = wks.Pictures(wks.Pictures.Count)
pic.Top = chtob.Top
pic.Left = chtob.Left
chtob.Delete
Next
Next
Set pic = Nothing
Set chtob = Nothing
Set wks = Nothing
End Sub

- Jon

 

[Tushar Mehta]

If one of the answers you've already received meet your requirements,
fine. If not...

It isn't clear what you want to do. In the first para, you indicate
you want to paste the chart as a linked object. In the last para, you
indicate you want to break the link. Leaves one kinda confused...

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions