I work in a reasonably large company (250+ employees).

Management has decided to implement style standards -
including excel chart standards.

I have produced the required charts and have them saved as
custom charts on my PC. I now have to distribute them
throughout the company. I can't just overwrite the
existing xlusrgal.xls, as some people have already created
some custom charts for themselves.

If I can write an executable that will copy the charts
from my xlusrgal.xls (which will be put in a standard
network drive) into the xlusrgal.xls on each users drive,
then the system administrators can get the executable to
run on each PC to do the update.

So I need to write the executable. I could write it in VB
(I have the experience required), but am concerned about
having the required dll's etc on the user's PCs.

Has anyone else ever done this, or does anyone simply have
any ideas about how it might best be done.

Thanks in Advance.
Derek

Derek,

Need dll's be involved?

If this is a one off could you ask relevant users
(presumably not all 250) to update their own xlusrgal.xls

If that's viable, distribute a file with sample
chartsheets with a macro something like this

Sub UpdateCharts()

Dim ChName As Variant, ChTip As Variant
ChName = Array("ChartA", "ChartB")
ChTip = Array("chartA tip", "chartB tip")

For n = 1 To 2
Charts(n).Activate
Application.AddChartAutoFormat _
Chart:=ActiveChart, Name:=ChName(n - 1), _
Description:=ChTip(n - 1)
Next n
End sub

Obviously test it first.

I should add I have not this before, and I wouldn't be
surprised if someone has a better idea!

Regards,

-----Original Message-----
I work in a reasonably large company (250+ employees).

Management has decided to implement style standards -
including excel chart standards.

I have produced the required charts and have them saved
as
custom charts on my PC. I now have to distribute them
throughout the company. I can't just overwrite the
existing xlusrgal.xls, as some people have already
created
some custom charts for themselves.

If I can write an executable that will copy the charts
from my xlusrgal.xls (which will be put in a standard
network drive) into the xlusrgal.xls on each users drive,
then the system administrators can get the executable to
run on each PC to do the update.

So I need to write the executable. I could write it in
VB
(I have the experience required), but am concerned about
having the required dll's etc on the user's PCs.

Has anyone else ever done this, or does anyone simply
have
any ideas about how it might best be done.

Thanks in Advance.
Derek
.

Sandy, Derek -

This is similar to a solution I tested and posted this winter:

http://www.google.com/groups?selm=eL...40TK2MSFTNGP10

If you run the macro from the Worksheet_Open procedure, anyone who opens
the attachment will get their user gallery updated.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Sandy V wrote:
Derek,

Need dll's be involved?

If this is a one off could you ask relevant users
(presumably not all 250) to update their own xlusrgal.xls

If that's viable, distribute a file with sample
chartsheets with a macro something like this

Sub UpdateCharts()

Dim ChName As Variant, ChTip As Variant
ChName = Array("ChartA", "ChartB")
ChTip = Array("chartA tip", "chartB tip")

For n = 1 To 2
Charts(n).Activate
Application.AddChartAutoFormat _
Chart:=ActiveChart, Name:=ChName(n - 1), _
Description:=ChTip(n - 1)
Next n
End sub

Obviously test it first.

I should add I have not this before, and I wouldn't be
surprised if someone has a better idea!

Regards,


-----Original Message-----
I work in a reasonably large company (250+ employees).

Management has decided to implement style standards -
including excel chart standards.

I have produced the required charts and have them saved

as

custom charts on my PC. I now have to distribute them
throughout the company. I can't just overwrite the
existing xlusrgal.xls, as some people have already

created

some custom charts for themselves.

If I can write an executable that will copy the charts

from my xlusrgal.xls (which will be put in a standard

network drive) into the xlusrgal.xls on each users drive,
then the system administrators can get the executable to
run on each PC to do the update.

So I need to write the executable. I could write it in

VB

(I have the experience required), but am concerned about
having the required dll's etc on the user's PCs.

Has anyone else ever done this, or does anyone simply

have

any ideas about how it might best be done.

Thanks in Advance.
Derek
.

Derek
Looks like Jon Peltier & I were following up at the same
time. As he has previously suggested similar but better
(I hadn't seen his "winter post") I think you can be more
confident this is the way to go.

Re use a different method
See Jon's code

Regards,
Sandy

PS to Jon
In our last exchange in May you mentioned
Isn't that fun.
to which I meant to reply - "No !"


-----Original Message-----
Hi Derek

I'll expand a little on my original suggestion, even
though it might not be what you want. First, I assume
from
your comments:
- all users have their own xlusrgal.xls
- you can't get / update / replace their xlusrgal.xls

Email all users with a workbook as previously described.
Rename the macro: Sub Auto_Open()

Then providing the user opens the attachment their
xlusrgal.xls will be updated (ie their own, not the
network but ensure). Also each user will have the
opportunity to see each new custom chart, perhaps with
additional comments as to their use.

I'm guessing most users will do this when received.
Within
the same macro you could include code to automatically
email you with "done" confirmation.
Eg
MsgString = " _
& "?subject= xlusrgal updated"
ActiveWorkbook.FollowHyperlink Address:=MsgString

If user's address in the From line is not enough,
search "Network Username" in this NG. In your email app
set up a filter to handle replies. Follow up the 10% (!)
of non-respondents.

Re original code:
If user moves / deletes chart-sheets the "arrays" will be
out of sync. Protect the workbook or use a different
method, eg Chart.Name. (Only a problem if saved, re-
opened
and re-run).

Instead of chart-sheets the samples could be embedded
charts if preferred. Re-work the code accordingly.

I don't think it will do any harm in the short term if
user re-opens the file thereby re-updating their
xlusrgal.xls. You could include an expiry,
eg
If Now 37848 + 14 Then Exit Sub '15 Aug 03 + 14

Probably worth including an error handler and changing
the
email "subject" accordingly.

I appreciate all this is not the approach you are looking
for, but in the absence of anything better maybe it will
suffice.

Regards,
Sandy

PS If you use this approach, I'd be interested to know
what % of users "respond" first time.

snipped

Hi Guys

I've just gotten back on to this after a break and applied
the logic you suggested in your winter post.

Everything worked fine until I opened a chart and tried to
change the chart type to the one that was added in the
macro.

I got the MS Office "Do you want to send a crash report to
Microsoft" error.

I can save this chart manually and then use it fine. It's
just the automatic one that is crashing.

Any ideas of what I could look at?

Thanks
Derek


-----Original Message-----


Sandy V wrote:
Derek
Looks like Jon Peltier & I were following up at the
same
time. As he has previously suggested similar but
better
(I hadn't seen his "winter post") I think you can be
more
confident this is the way to go.

Re use a different method
See Jon's code

Regards,
Sandy

PS to Jon
In our last exchange in May you mentioned

Isn't that fun.

to which I meant to reply - "No !"


Sandy -

I don't remember, but my tone of voice sounds familiar!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


.

Hi Derek,

I opened a chart and tried to change the chart type
to the one that was added in the macro. /../
It's just the automatic one that is crashing.

I don't quite follow. Do you mean crashes when changing a
standard type chart to a new "User defined" type that's
just been updated in xlusrgal.xls?

What happens if you create a new chart of the type that
crashed when changing an existing chart?

Don't know if this is relevant:
I notice when xlusrgal.xls updates so does Xl8galry.xls,
seems they work as a pair. The latter stores the "Built in
Custom" types, they are both in the same (sub) directory
of /Office.

I wonder if there is any network confusion. Depending on
whether the user is/not logged on, which pair of these
chart files is accessed/updated. Might be worth
experimenting and looking at file dates of the user's and
networks pair of chart files.

Another thing to try:
Load your file to another terminal but disable macros or
beforehand rename the Auto_open()
Manually add the new custom charts, in effect do the same
as the macro being careful to use same names. You should
end up with the same problem, but if not should give a
clue.

Regards,
Sandy

-----Original Message-----
Hi Guys

I've just gotten back on to this after a break and
applied
the logic you suggested in your winter post.

Everything worked fine until I opened a chart and tried
to
change the chart type to the one that was added in the
macro.

I got the MS Office "Do you want to send a crash report
to
Microsoft" error.

I can save this chart manually and then use it fine.
It's
just the automatic one that is crashing.

Any ideas of what I could look at?

Thanks
Derek


-----Original Message-----


Sandy V wrote:
Derek
Looks like Jon Peltier & I were following up at the
same
time. As he has previously suggested similar but
better
(I hadn't seen his "winter post") I think you can be
more
confident this is the way to go.

Re use a different method
See Jon's code

Regards,
Sandy

PS to Jon
In our last exchange in May you mentioned

Isn't that fun.

to which I meant to reply - "No !"


Sandy -

I don't remember, but my tone of voice sounds familiar!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Hi Derek

when you use one of the saved charts
the fonts all play up.

Again I don't quite follow, what do you mean by "use". Do
you mean changing an old chart to new custom type. I doubt
if the problem is directly related to "update" macro which
(I assume) only does what you could have done manually.
Might be worth a repost describing the steps that lead to
the problem.

Just a guess, something to do with Font Autoscale perhaps.

BTW, did you distribute the update to 250 users, if so
what proportion responded. Also what was the
cause/solution to the problems you described on Aug 26th.

Regards,
Sandy

-----Original Message-----
Hi Guys

I now have most of the work done. However occasionally
it
is taking the chart and reducing all the fonts down to
3.5
or other rediculous values.

The graphs appear fine in the excel spreadsheet that I'm
going to have people open, but when you use one of the
saved charts the fonts all play up.

Help.

Derek


-----Original Message-----
Hi Derek,

I opened a chart and tried to change the chart type
to the one that was added in the macro. /../
It's just the automatic one that is crashing.

I don't quite follow. Do you mean crashes when changing
a
standard type chart to a new "User defined" type that's
just been updated in xlusrgal.xls?

What happens if you create a new chart of the type that
crashed when changing an existing chart?

Don't know if this is relevant:
I notice when xlusrgal.xls updates so does Xl8galry.xls,
seems they work as a pair. The latter stores the "Built
in
Custom" types, they are both in the same (sub) directory
of /Office.

I wonder if there is any network confusion. Depending on
whether the user is/not logged on, which pair of these
chart files is accessed/updated. Might be worth
experimenting and looking at file dates of the user's
and
networks pair of chart files.

Another thing to try:
Load your file to another terminal but disable macros or
beforehand rename the Auto_open()
Manually add the new custom charts, in effect do the
same
as the macro being careful to use same names. You should
end up with the same problem, but if not should give a
clue.

Regards,
Sandy

-----Original Message-----
Hi Guys

I've just gotten back on to this after a break and
applied
the logic you suggested in your winter post.

Everything worked fine until I opened a chart and tried
to
change the chart type to the one that was added in the
macro.

I got the MS Office "Do you want to send a crash report
to
Microsoft" error.

I can save this chart manually and then use it fine.
It's
just the automatic one that is crashing.

Any ideas of what I could look at?

Thanks
Derek


-----Original Message-----


Sandy V wrote:
Derek
Looks like Jon Peltier & I were following up at the
same
time. As he has previously suggested similar but
better
(I hadn't seen his "winter post") I think you can be
more
confident this is the way to go.

Re use a different method
See Jon's code

Regards,
Sandy

PS to Jon
In our last exchange in May you mentioned

Isn't that fun.

to which I meant to reply - "No !"


Sandy -

I don't remember, but my tone of voice sounds familiar!

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______


.

.