Run a macro on multiple worksheets

[pinkfish.jm]

Hi,
I have excel documents that are automatically generated, and I'd like
to run a macros on them when they first open. I want to store the macro
in one separate file, then have it automatically included in all my
excel worksheets.

1) Can I store a macro in such a way where it can be automatically
included in other files? (I don't want to use the personal.xls file
because the files will be used by multiple users)

2) Ideally, it'd be nice to use applescript to include and run the
macro in all the worksheets, but I don't know how to export a macro.

Thanks!

 

[little_creature]

Hi, I will answer part of your question.
If you use *Private Sub* instead of *Sub* this macro will run automatically
on open of document when this macro is stored in. If you use Personal.xls
which is launched whenever Excel starts this macro will be applied on all
document being opened.
Unfortunately I'm not much familiar with Apple script so I have no answr
for that question. Hope this will hepl.

 

[pinkfish.jm]

The problem is the macro needs to be run on other files (not just the
one it's included in). The macro needs to be stored in a separate file
and automatically included in other files. I need to find a way to
transfer/copy the macro over.

Thanks.

 

[Jim Gordon MVP]

Hi

To have a Macro run when a workbook is opened you name the sub Autpen()

Macros in one worbook can run macros in other workbooks. You can have things
set up just the way you envisioned.

Macros themselves are just text. So you can save a macro as a text file.
Then you can import the text file into the Visual Basic Editor.

When in the VB Editor just used Edit > Select All then Edit > Copy. Paste
the result into a word processor (Microsoft Word, for instance) and save the
result as text with a .txt file extension.

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[Paul Berkowitz]

2) Ideally, it'd be nice to use applescript to include and run the
macro in all the worksheets, but I don't know how to export a macro.

It would make more sense = if you are going to use AppleScript - to write
the whole thing in AppleScript, not as a VBA macro. It could be written so
as to work on whichever workbooks you wanted. It would also have the
advantage of continuing to work in the next version of Excel which will not
have VBA. It would have the disadvantage that there is no equivalent for
Auto_Macro(). It can't run automatically on open - you have to choose to run
the script (kept in the script menu or as a script application). Hopefully
an auto-open feature might be added in the next version of Office.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[gimme_this_gimme_that]

Look up Excel AddIns. An AddIn is the separate file you're thinking of.
That will give you a start anyway.

 

[pinkfish.jm]

Thanks for everyone's help! Time to brush up on Applescript.