Macro to update other local macros

[Snoopy]

Hey Guys
I have made a report-system that collects som summaries from a set of
users. In each local user Excel-file there is a macro that does stuff.
Sometimes I have to update this "local" macro - and have to ask every
user to replace the macro update manually (copy/paste in VBA).
Its not that big effort, but it would be more "proffessional"
expression if I could send over an automatic update sort-of-macro by
mail.

The update macro
1) I -as administrator - will chose the name of the macro to be
updated
2) I send thr update to the user
2) The user chose the local receiving file from the explorer/open-file
manager
3) My macro updates the local macro by running this update macro

Is this possible and how do I do it?

Example

Local file Report.xlsm contains a macro UpdatePivotFields
__________________
Best Regards
Snoopy

 

[Alan]

Hey Guys
I have made a report-system that collects som summaries from a set of
users. In each local user Excel-file there is a macro that does stuff.
Sometimes I have to update this "local" macro - and have to ask every
user to replace the macro update manually (copy/paste in VBA).
Its not that big effort, but it would be more "proffessional"
expression if I could send over an automatic update sort-of-macro by
mail.

The update macro
1) I -as administrator - will chose the name of the macro to be
updated
2) I send thr update to the user
2) The user chose the local receiving file from the explorer/open-file
manager
3) My macro updates the local macro by running this update macro

Is this possible and how do I do it?

Example

Local file Report.xlsm contains a macro UpdatePivotFields
__________________
Best Regards
Snoopy

Have you considered putting all of the 'local' code into a single add-
in which is available to all. When changes are required simply reissue
the updated add-in to all of your users i.e. replace the whole file
rather than getting your users to cut/paste individual lines of code -
their workbooks remain untouched as long as the reissued add-in has
the same name as the previous version.

A.

 

[Snoopy]

Have you considered putting all of the 'local' code into a single add-
in which is available to all. When changes are required simply reissue
the updated add-in to all of your users i.e. replace the whole file
rather than getting your users to cut/paste individual lines of code -
their workbooks remain untouched as long as the reissued add-in has
the same name as the previous version.

A.– Skjul sitert tekst –

– Vis sitert tekst –

Hey - thanks!
Please see my posted thought above.
I think I am moving in right direction, but need some confirmation and
guiding

Best Regards
Snoopy

 

[bbpw4gzus]

Hey - thanks!
Please see my posted thought above.
I think I am moving in right direction, but need some confirmation and
guiding

Best Regards
Snoopy- Hide quoted text -

- Show quoted text -

What you are explaining cannot be done, Best way to do thist to keep
the users from updating it thierselves, is to add each user with thier
own file on a server, and have their file linked to their specific
file on the server. When you have to update their macro, you do the
update and when they open the file, it will update on their computer.
This is the best way to do it, a bit more work for you, but it will be
more professional. i agree with you having the user update something
that is technically your job is a bit unprofessional.

 

[Alan]

What you are explaining cannot be done, Best way to do thist to keep
the users from updating it thierselves, is to add each user with thier
own file on a server, and have their file linked to their specific
file on the server. When you have to update their macro, you do the
update and when they open the file, it will update on their computer.
This is the best way to do it, a bit more work for you, but it will be
more professional. i agree with you having the user update something
that is technically your job is a bit unprofessional.- Hide quoted text -

- Show quoted text -

This is simply untrue! A common add-in CAN be used for all users. A
small amount of code (that does not need editing) would be used in
each user workbook to either call common code from the add-in or
specific routines which are only used by that particular workbook. I
wish everything that cannot be done was this easy!

 

[Martin Brown]

Hey Guys
I have made a report-system that collects som summaries from a set of
users. In each local user Excel-file there is a macro that does stuff.
Sometimes I have to update this "local" macro - and have to ask every
user to replace the macro update manually (copy/paste in VBA).
Its not that big effort, but it would be more "proffessional"
expression if I could send over an automatic update sort-of-macro by
mail.

The update macro
1) I -as administrator - will chose the name of the macro to be
updated
2) I send thr update to the user
2) The user chose the local receiving file from the explorer/open-file
manager
3) My macro updates the local macro by running this update macro

Is this possible and how do I do it?

It is possible but it would require that users have their security
settings very weak to permit running code that directly alters VBA and
exposes the VBA programming model (not default settings in XL2007/10).

Example

Local file Report.xlsm contains a macro UpdatePivotFields

As another poster has pointed out you would be better off putting all
the common code that is likely to alter into an AddIn and linking the
local worksheets to that. Then when you replace that master AddIn file
all linked copies are updated without any effort by your users.

Regards,
Martin Brown