Bypass "DISable Macros"
 

I have created a "template" (not in the true sense of the word for
excel, it is still an .xls file, not an .xlt) that has been used to
create ~1000 spreadsheets. The template contains a lot of VBA code.
However, I would now like to update that code, and have the change
reflected in all of the old workbooks. I am attempting to do this
update through macros that are in a separate workbook, which I call
ImportExport.xls. The update (in pseudocode)looks like:

For each file in each folder...
If workbook needs to be updated then
open workbook
remove old code
import new code
save workbook
close workbook
End if
Next


Finally we get to the problem. I can't have the workbooks that I'm
updating enable their macros while I'm updating them. If they were to
be enabled and run their workbook open, workbook close, and workbook
save events, it would take days for the update to finish.

If I set macro security to high, I need to close and reopen excel for
the change to take place, and then I can't run my update macros. Is
there any way to disable macros when opening them through VBA?

Also, am I really dumb for not making my "template" an add-in in the
first place? I was originally hesitant to do that because the
"template" is on the network drive, and then I could have one instance
of the "template" for every computer that might want to use my file,
and not have to worry about installing the add-in on everyone's
machine (~30 machines). Any advice on any of the above would be
helpful.

Thanks,
Chip

Bypass "DISable Macros"
 

Hi
only as an idea: Disable the events while running your macro with
application.enableevents=false

this will prevent running the workbook_open event


--
Regards
Frank Kabel
Frankfurt, Germany


Chip R. wrote:
I have created a "template" (not in the true sense of the word for
excel, it is still an .xls file, not an .xlt) that has been used to
create ~1000 spreadsheets. The template contains a lot of VBA code.
However, I would now like to update that code, and have the change
reflected in all of the old workbooks. I am attempting to do this
update through macros that are in a separate workbook, which I call
ImportExport.xls. The update (in pseudocode)looks like:

For each file in each folder...
If workbook needs to be updated then
open workbook
remove old code
import new code
save workbook
close workbook
End if
Next


Finally we get to the problem. I can't have the workbooks that I'm
updating enable their macros while I'm updating them. If they were
to
be enabled and run their workbook open, workbook close, and workbook
save events, it would take days for the update to finish.

If I set macro security to high, I need to close and reopen excel for
the change to take place, and then I can't run my update macros. Is
there any way to disable macros when opening them through VBA?

Also, am I really dumb for not making my "template" an add-in in the
first place? I was originally hesitant to do that because the
"template" is on the network drive, and then I could have one
instance
of the "template" for every computer that might want to use my file,
and not have to worry about installing the add-in on everyone's
machine (~30 machines). Any advice on any of the above would be
helpful.

Thanks,
Chip

Bypass "DISable Macros"
 

Frank,

Thanks for the excellent suggestion! This certainly seems to be
preventing the open, save, and close events from running.

-Chip

"Frank Kabel" wrote in message ...
Hi
only as an idea: Disable the events while running your macro with
application.enableevents=false

this will prevent running the workbook_open event


--
Regards
Frank Kabel
Frankfurt, Germany