Copying chart templates to other workbook - Links

[rukkie]

Hi,

I have something "strange " (at least for me) going on ...

I have an xls workbook, containing some data sheets and also 3 chart
templates. These chart templates are copied via a macro to the output
xls with the statement :

Sheets(ChartName) _
.Copy Before:=Workbooks(targetfile.Window).Sheets(1)

When I now look to this chart template in the output xls I got a link
to the original xls workbook in which the macro is running. (I can see
that via EDIT>LINKS...)

Now I do NOT want that link to the originator xls workbook at all !

Is there anybody who knows how I can copy a chart template to another
workbook without the link (reference) to original workbook ?

Any help is very appreciated !!

 

[Tom Ogilvy]

Copy the source data as well, but copy the two sheets together.


Sheets(Array(ChartName,DataName)) _
.Copy Before:=Workbooks(targetfile.Window).Sheets(1)

 

[rukkie]

Hi,

are there no other possibilities than copying also the source data ?
People for who the output is generated are not interested in this
source data ...Otherwise I could give them also the originator xls
workbook and so EXCEL will never complain about "File not found" ... I
just want to avoid that they got the "overload" of sheets/data which
are not interesting for them ...

I hope you understand ...

 

[Tom Ogilvy]

http://support.microsoft.com/kb/213443/en-us
XL2000: Visual Basic Macro to Break Chart Links

you would do this after you copy the chart.

The other alternative would be to copy as picture and paste the chart in as
a picture.

 

[Peter T]

Another possibility with XP or later (suggested by Jon Peltier elsewhere)
Edit > Links > Break Link

The link Tom referred you to should work fine with typical smallish charts,
without special number formats in say axes or data labels, no linked titles
or labels, and won't work for all types of charts. There's also a max 255
characters limit (incl additional ancillary characters) per set of values
and can be less than 255, though I guess that's unlikely in a template
chart.

If neither Tom's link nor Jon's suggestion work fully as expected revert
back, I have something else you could try.

Regards,
Peter T