User-defined chart type deployment

[Jeff K]

In Excel 2002, under XP professional, I have an application that uses custom chart types (User-defined) stored in the xlusrgal.xls file. I would like the application to use this file, but I do not want to overwrite any existing custom chart types. I want the application to use my xlusrgal only when the application needs it and leave any other xlusrgal file alone. In prior versions of Excel, I was able to accomplish by setting/resetting the alt startup path after program initiation and storing the xlusrgal file there. This does not seem to work anymore. Any workarounds? (Thanks in advance.)

 

[Jon Peltier]

Jeff -

Here's how to distribute those custom chart types.

Embed each custom chart template on its own worksheet. Put the name for
this chart type in cell A1 of the sheet, and the description in cell A2.

Put this macro into a regular code module in the workbook containing
your custom chart templates:

Sub DistributeChartTypes()
Dim ws As Worksheet
Dim cht As Chart
Dim chto As ChartObject
For Each ws In ActiveWorkbook.Worksheets
If ws.ChartObjects.Count > 0 Then
If Len(ws.Cells(1, 1).Value) > 1 Then
Set cht = ws.ChartObjects(1).Chart
Application.AddChartAutoFormat Chart:=cht, _
Name:=ws.Cells(1, 1).Value, _
Description:=ws.Cells(2, 1)
End If
End If
Next
End Sub

Finally, put this macro into the "ThisWorkbook" code module of the
workbook with the custom charts:

Private Sub Workbook_Open()
DistributeChartTypes
End Sub

Send out the file with instructions to open it with macros enabled. The
Workbook_Open procedure will run when the workbook opens, which runs the
DistributeChartTypes procedure. If any chart types with the same name
is present, the new one will overwrite the existing one.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______