Linked Charts in Excel 2000

  • Thread starter Thread starter Don R
  • Start date Start date
D

Don R

I have an application that creates a multiple charts using
data from a separate workbook. The chart workbook is sent
to users at a another location. In the tools options edit
menu I have uncheck "ask to update automatic links". When
users open the chart workbook, it displays the File not
found dialog box with the data workbook as the file. The
users can click Cancel to not update the links, but I
would like the chart file to not display the file not
found dialog. Excel XP has a start up prompt button that
lets you control the updating of the links. Does this
feature exist in Excel 2000? Any ideas how to stop the
file not found dialog box not to display?

Thanks.
 
Hi Don,

It is probably a better idea to send a worksheet with
embedded charts that are not linked to any other sheet.

That way not only will users not be prompted to update
links but will not be able to update charts accidentally.

The small macro below copies the active sheet as a new
sheet, then converts all charts on the sheet to value
series (sort of like doing Paste Special Values on a
worksheet)

Option Explicit

Sub CopyChartSheet()
Dim cht As ChartObject
Dim ser As Series


' copy to new sheet
ActiveSheet.Copy Before:=Worksheets(1)

' iterate through each chart and each series
' in the new worksheet
For Each cht In ActiveSheet.ChartObjects
For Each ser In cht.Chart.SeriesCollection
' copy value array, xValue array, and name
' as values
ser.Values = ser.Values
ser.XValues = ser.XValues
ser.Name = ser.Name
Next ser
Next cht

End Sub
 
Ed's approach is one way to go. If you do, you might want to extend
his code to include other potential links - chart/axis titles,
textboxes, data labels, error bars, ??

Also, keep in mind that there is a limit on the number of characters in
the SERIES formula. Replacing range references with actual values
might blow that limit.

An alternative is to publish an image of the chart. Make a *copy* of
the chart workbook.

*** If you don't work with a copy of the chart workbook, you will lose
all your charts! ***

In this workbook that is a copy of the chart workbook, select each
chart, then select SHIFT+Edit|Copy Picture..., specify desired
attributes, and paste. Delete the linked chart. Distribute this new
workbook.

To automate the process, turn on the macro recorder (Tools | Macro >
Record new macro...), do the above, and turn off the recorder. XL will
give you the necessary starter code.

--
Regards,

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