Porting a macro to 500 users

[Bishop]

I have written a macro that I need to give to 500+ other end users in my
company. The code would have to go in their Personal.xls workbook so they
could run it for any Excel file they open. What's the best way to do this?

 

[Dave Peterson]

It doesn't have to go in their personal.xls workbook.

In fact, I wouldn't want you touching my file. After I make changes to my
development version and update the real one, your macros will be gone. In fact,
I protect my personal.xl* file (marked readonly) and I don't allow programs to
access the project, either.

Instead...

I'd create a new workbook with just the code in it.
Save it as BishopUtils.xls (or as an addin *.xla).
Store it on a common network drive and tell the users to open the workbook when
they need to have access to your macros.

If the common network drive isn't available, then I'd tell the users to create a
dedicated folder on their local drive:

C:\ExcelUtilities
and put the file there.

This will make sharing workbooks that use user defined functions (in worksheet
cells) easier to share between your users. (You may have problems with links
pointing to the wrong folder if people put the file in a location that doesn't
share that common name.)

Personally, I'd save the file as an addin (*.xla or *.xlam). But then I'd have
to give the user a way to run the macros.

Saved from a previous post:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/archive/2010/03/27/deploy-me-simple.aspx

 

[Barb Reinhardt]

If it were me, I'd probably distribute it as an add-in. Can your IT group
distribute it to all of them with LanDesk or something similar? I've only
distributed a couple of add-ins to individuals, not a group, so I'm not sure
I'm the best to answer this. I'm sure others will weigh in on your question.

 

[Bishop]

Dave

Thank you for your response. Really good information in here. I tried your
suggested approach about storing the macro in a workbook and making the
workbook available to users when they want to run the macro. However, the
Big Boss said I had to "accommodate the lowest common denominator." Which is
to say that some of our end users are not exactly Excel proficient. He felt
that asking them to open a second workbook would be asking too much. So now
what I have to do is make the following happen:

//these first 4 instructions the end user can do. I'm Providing the steps
leading up
//to my task to make it easier to understand.

An end user goes to XYZ website.
Opens a file.
Clicks "Export file to Excel."
An Excel .xls workbook opens with the information.

//Here's where I come in
When the Excel workbook opens a toolbar must be added
When the toolbar is added it must have a button
When the button is pushed it must execute a macro
The macro will format the spreadsheet to meet specifications
When the workbook is closed the toolbar is unloaded

So I have written the code to load the toolbar, button and the macro to
format. It is in a workbook on my desktop right now. The reason I mentioned
adding it to all the end users Personal.xls file is because that's the only
way I know to accommodate a request like this. Big Boss is not budging so I
have to make this work. I'm sure I'll have to write up a very detailed set
of instructions for the end users to follow to add this macro to their
Personal.xls file but I wanted to make sure I'm covering all my bases first.
Do you have a better suggestion to implement what's being requested?

 

[Bishop]

And I am using 2007 btw.

It doesn't have to go in their personal.xls workbook.

In fact, I wouldn't want you touching my file. After I make changes to my
development version and update the real one, your macros will be gone. In fact,
I protect my personal.xl* file (marked readonly) and I don't allow programs to
access the project, either.

Instead...

I'd create a new workbook with just the code in it.
Save it as BishopUtils.xls (or as an addin *.xla).
Store it on a common network drive and tell the users to open the workbook when
they need to have access to your macros.

If the common network drive isn't available, then I'd tell the users to create a
dedicated folder on their local drive:

C:\ExcelUtilities
and put the file there.

This will make sharing workbooks that use user defined functions (in worksheet
cells) easier to share between your users. (You may have problems with links
pointing to the wrong folder if people put the file in a location that doesn't
share that common name.)

Personally, I'd save the file as an addin (*.xla or *.xlam). But then I'd have
to give the user a way to run the macros.

Saved from a previous post:

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/archive/2010/03/27/deploy-me-simple.aspx

 

[Dave Peterson]

It still applies.

And I am using 2007 btw.