sharing macros with other users

[DNader]

I use Access to create a report each month and then export it into Excel. I
have set up a macro that does a couple of calculations and formats the file
for printing. I'd like to be able to share that macro with another co-worker
so he can run the report as well. If I make a change to the macro, I would
like the change to be available to anyone who runs that report for the month.
Is there a way to share Excel macros by setting up something like a
user-group?

 

[Dave Peterson]

How about creating a dedicated workbook with your macro code.

Save the file to a common network folder so that anyone can open it when they
need it. (And save a backup for you!)

Then if you need to update it, just make your changes on your version and then
copy it to the common network folder.

 

[DNader]

Thank you for your response. After putting my head together with another
co-worker, we came up with a solution similar to the one you mentioned.

I copied my "Personal.xls" (which is where Excel stores your macros) to a
shared folder on a network drive. I found this folder in the following path:
C:\Documents and Settings\Application Data\Microsoft\Excel\XLSTART

I changed the name of the Personal.xls file on the network to Shared.xls, so
it would be easier to identify later.

I then made the following change in Excel:
Under "Tools\Options\General", I place the path and folder name in the field
labeled "At startup, open all files in: "

Now, my co-workers and I can share macros as long as we save them in our
"Shared.xls" file.

 

[Whitney]

What path do you enter under Tools>Options>General?

I saved the file to a shared drive on N. So I entered N:\SHARED.XLS , closed
excel and reopened it, but the macro was not visible.