MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Macro works, but only on my PC

  Asked By: Molly    Date: Feb 02    Category: MS Office    Views: 1937

I have a macro, in my Personal.Xls, which will properly format a
very "ugly" csv file into a usable clean xls spreadsheet.

The problem: how do I get the same macro to my end-users? Is it
portable? Can I store it like a file on a server and have them access
it and copy it to their XLS?

Or, must I recode or copy/paste it into every user's PC?



5 Answers Found

Answer #1    Answered By: Adalwine Fischer     Answered On: Feb 02

How do you WANT them to use it.
There are drawbacks to all the methods for distributing macros.
Whatever method you use, you're going to HAVE to take it out of your
Personal.xls file  and save it somewhere else.

If you put the macro  into an Excel file and save it as .xla,
then send each user a link to the file,
when they double-click on the link, it will install an Excel "add-in".
The problem  with this approach is that it actually COPIES the xla file
to their configured folders.
If you have to make a change or repair to the code, they won't get it.

another way is to put it in an standard Excel file (.xls) and send a link (or
You can make it so that when they open the file, your macro automatically runs.
The nice thing about this approach is that you can keep a "working copy"
so that you can modify it and test it. Then copy  and overwrite "their" file and
shortcuts will automatically point to the new and improved version.

Yet a third approach would be to name your module something "unique".
Then, export the macro to a .bas file in a shared location.
Next, create a macro and save it in a .xla file that looks through the modules
and removes the one named "your unique name".
Then, import the module from the shared location and execute your macro.

These are just a few of many, MANY combinations of approaches to distributing
your code.
The fundamental question is, however, how do you want them to USE it.
Do you expect them to turn on the vba toolbar, and hit the "run" icon?
Will the use the menus and select Tools -> Macro -> Macros.
Do you want to have a button?

Deciding how you want them to fire it off will help to narrow down the decision
on how to distribute the code.

Answer #2    Answered By: Kristin Johnston     Answered On: Feb 02

I'm logged onto my PC at home. As I siee it,
all I want them to do is open a csv, which will always be in the same layout,
and press CTRL and a letter to format  a usable report. Each one of these options
sounds like the answer. Knowing the reason for the macro, which would you

Answer #3    Answered By: Beatriz Silva     Answered On: Feb 02

I take it that the Ctrl-letter keystroke is assigned
to the macros you've written to create the report.

What I would recommend is moving these macros to
an Excel file. Use either the Auto_Open sub or the
Worksheet_Open event to open the Csv file.

Then, distribute a shortcut to your new Excel file.

Answer #4    Answered By: Yvonne Watkins     Answered On: Feb 02

This is a tremendous help. Thanks!!!

Answer #5    Answered By: Yvette Griffin     Answered On: Feb 02

I use a toolbar setup in a workbook module that installs a toolbar on the
user's computer in an Auto_Open event. The toolbar code is followed by the macro
code. In the toolbar setup code, set the button(s) to run the macro(s) contained
in the same module. When the toolbar installs, it automatically links the path,
file, and macro  location to the button(s) and make it(them) available to the
user. If modifications need to be made to the macro(s), they are made in one
location and does not affect any user that has the toolbar installed. As long as
the path/file/module/macro names remain the same, any toolbar installed will
always work correctly. If the file  is moved, renamed, etc... for some reason, a
link to the new file location can be sent to users. They open the file once,
install the new toolbar, which also deletes the old toolbar, the link is
recreated. I have several files currently beings used by my group and they
couldn't be happier. If someone asks me if I can pluck some other type of data
out of a spreadsheet, I copy  the current file, add the code, and paste the file
over the current copy. If it requires another button, then I do the same thing,
only I add another button to the toolbar setup code and send the users  a link
that will open the file and install the new toolbar. The next time anyone
accesses the macro they see the change. I use a MsgBox with vbYes vbNo to ask
the user if they want to install the toolbar as a way to prevent it from
installing inadvertently and to keep the file open. I use the vbNo to stop the
macro, leaving the workbook open for changes. vbYes installs the toolbar and
closes using ThisWorkbook.Close. I always include a Remove Toobar button on
every toolbar.

Didn't find what you were looking for? Find more on Macro works, but only on my PC Or get search suggestion and latest updates.