Accessing the PERSONAL.XLS without a macro ever recorded by user.

[cmarion]

I am in a networking environment and we are recordingmacros to run on
standard, .csv reports for limited usage by individuals. Most people
recording the initial macros are beginner to intermediate users trained to
use the macro recorder and we are trying to keep it simple. So far the
easiest way to distribute the macro is to open the Visual Basic Editor,
export the module with the macro, email it or copy to the end user, then
import the .bas file onto specific user's computer.

Our problem is that most users have never created a macro, let alone one
saved in the personal macro workbook and therefore there is no PERSONAL.XLS.
The work-around is to record a short macro in the Personal Macro Workbook,
thus creating the PERSONAL.XLS. Is there a more elegant solution?

Copying someone's PERSONAL.XLS to another computer copies too many other
macros.

 

[Dave Peterson]

I think I would set up a coordinator who would review the code to make sure it
worked ok.

Then that person would be responsible for consolidating all the macros into a
single workbook (or addin). Then that single workbook/addin would be located on
a common network drive (or distributed to each user).

If you make it an addin, you'll have to give the users a way to run the macro.

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)

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

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

 

[cmarion]

This is a very large environment and these macros are used by 1-3 people,
simply automating report formatting. Our goal is not to create enterprise or
even department wide macros but train individuals who create and record
simple macros. Then share the macro with others who are often temps or lower
skilled people who simply need to run the macro on the report.

The current way we distribute the macro is easy for the user, does not
involve navigating the labyrinth of network/user/shared drives or user
settings. We can give them basic how to, to import the macro module in to the
VBE.

But we often run into no Personal.xls on the end users machine. Then someone
with macro knowledge has to create a simple macro simply to create the
Personal.xls. Then import the module. Is there another way to create a
personal.xls oon a new install type of situation?

 

[Dave Peterson]

First, there's nothing really special about using the name personal.xls. The
reason I would avoid it is that some individuals could have their own set of
macros in their own personal.xls.

If you share "your" personal.xls workbook with them, then they'll have to make a
choice which file to keep open--excel can only have one file named personal.xls
open at one time.

It can quickly become a pain for users who have their own personal.xls files.

I wouldn't do this if I were you.