Calling Macros from another sheet without any hard coded references

[keilan.knight]

Hi,

I am working with a lot of worksheets which sit on a network share and
are accessed by various users, all these users have macro settings to
High and I don't want them having access to embedded macros, as I need
to have a batch job run on a stand alone PC which will run a specific
macro on all these sheets every week.

I use a scheduled VBS script to launch the sheets and run the macros.
What I would like to do is remove all the macros from the worksheets as
the security level provides a nag screen each time a new user opens the
sheet. Ideally I would like one XLS file sitting on the stand alone PC
which opens all the sheets and then runs the macros from one file. Is
this possible? (does it make sense?) I know there are workarounds such
as digitally signing macros etc..although as its the same macro in a
number of sheets, I'd rather only edit it once, and I want to eliminate
the macro security pop up box. Anyone have any ideas?

Thanks!

Keilan

 

[Bob Phillips]

Why don't you just put the macros in an add-in?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

hi,
yes i have a idea. i do this every morning, run and update
11 excel files.
I created a excel file on my pc call morningroutine.xls
i put all the macro in morningroutine.
the macros open up the appropreate file, updates by
downloading data into the files plus perform other
functions such as formating. four of the file are printed.
i have it set up to run at 5:00am (while i am still home
asleep) so that when i get to work, the reports are on the
printer. mornningroutine skips weekends.
the files have no macros in them so when others open up
one of the files, they don't get the macro warnings.

 

[keilan.knight]

That sounds an interesting idea...the sheets themself use pivot tables,
so I'm not sure how it all works (I *hate* excel!! but thats deff an
idea I will try out, thanks for the hints!

Keilan

 

[keilan.knight]

Hi,

That idea worked great! Thanks for that....I'm gonna have to construct
one super huge file but its a neat way of getting the job done!

For anyone (like myself) completely new to this stuff, heres how I did
it...

Sub UpdateExtSheet1()
' Open External Sheet
Workbooks.Open Filename:="G:\sheet1.xls"
Windows("sheet1.xls").Activate
' Macro I wish to run goes here
---------------------
' Macro ended

' Save and close external sheet

ActiveWorkbook.Save
ActiveWindow.Close


Thanks again
Keilan