Chart Template Default Directroy excel 2007

[Boxey]

I used to be able save a User Defined Chart Type to file XLUSRGAL.XLS
in Excel 97/03 on my (otherwise heavily restricted) Company Network
and use VBA to read and apply usertypes to xy scatter charts (up to
20 series to multi-format error bars and spoof markers etc). Very
powerful I think. I could see the location of XLUSRGAL.xls in my
personal profile.

However my Company is moving to a Citrix thin client desktop with
upgrade to Excel 2007. I am restricted from writing to, and reading
from, the Chart Template default directory. I suspect that might
remain the case for a very long time

Is there any way Excel 2007 could be redirected programatically or
otherwise to read and apply Chart Templates from a directory I CAN
create and save to?

One other thought: I don't use Chart.Add to generate the 6 charts I
create daily.I use Chart .Copy. I copy a hidden chart 6 times with
everything selectable on it deleted. Then basically some tidied up
code from a macro including applying the usertype. This was to repeate
some event programming (mouse_down etc) withut having to get into
classes.Could I use this technique to replicate my chart type. I have
a copy of XLUSRGAL.XLS

Thanks

 

[Jon Peltier]

If you're doing this programmatically, you can put the template wherever you
want, so long as the IT militia have given you some directories you can
write to. Put your chart in a workbook as the only sheet in the workbook,
and save the workbook as a macro-enabled Excel 2007 workbook, in a directory
you have permission to access. Then use this syntax to insert the chart:

Sub InsertAChart()
ActiveWorkbook.Sheets.Add _
Type:="C:\Documents and Settings\[username]\My
Documents\Templates\MyChart.xlsm"
End Sub

- Jon