Delete macros from 1000 documents

[Pat Hartman \(MVP\)]

I am an experienced Access VBA developer but the word object model has
defeated me. I need to do three things.
1. Document the macros in all the word documents in a folder.
2. Delete a particular macro from those documents (hopefully it always has
the same name).
3. During Automation from Access add the macro back in and have it run if
the document is printed.

Background. The client currently maintains two libraries of documents. One
is used with an excel application that automats word and fills bookmarks
with data. The other library is used for all other purposes. Maintaining
the separate libraries is quite an administrative burden and the client
would like to merge them. I am also replacing the Excel application with an
Access application that performs similar functions. Data capture (and
storage) and document printing after filling bookmarked fields. When the
documents are filled from the Access application, a macro that checks that
all bookmarks have data before allowing printing needs to run if the user
attempts to print the document. However, this macro is getting in the way
of other uses of the documents, hence the request to delete the existing
macro.

So far I have been able to print the names of the code modules with:
Application.VBE.VBProjects(1).VBComponents(i).Name
But, I can't drill down to the procedures within. Modules and macros are
exposed in the Access object model but they don't seem to be in the Word
object model. Can anyone help?

 

[Jonathan West]

I am an experienced Access VBA developer but the word object model has
defeated me. I need to do three things.
1. Document the macros in all the word documents in a folder.
2. Delete a particular macro from those documents (hopefully it always has
the same name).
3. During Automation from Access add the macro back in and have it run if
the document is printed.

Background. The client currently maintains two libraries of documents.
One is used with an excel application that automats word and fills
bookmarks with data. The other library is used for all other purposes.
Maintaining the separate libraries is quite an administrative burden and
the client would like to merge them. I am also replacing the Excel
application with an Access application that performs similar functions.
Data capture (and storage) and document printing after filling bookmarked
fields. When the documents are filled from the Access application, a
macro that checks that all bookmarks have data before allowing printing
needs to run if the user attempts to print the document. However, this
macro is getting in the way of other uses of the documents, hence the
request to delete the existing macro.

So far I have been able to print the names of the code modules with:
Application.VBE.VBProjects(1).VBComponents(i).Name
But, I can't drill down to the procedures within. Modules and macros are
exposed in the Access object model but they don't seem to be in the Word
object model. Can anyone help?

This is a difficult one. The problem here is that each template is a
separate project. You won't be able to get at the VBProject object at all
unless (assuming you are using Office 2003) you have the "Trust access to
the VB project" box checked in the Macro, Security dialog. That box can only
be checked by hand, not set through code. It is unchecked by default.

Even if the box is unchecked, the VBProject object of another template will
not be accessible to you if the author of the template has checked the "Lock
Project for viewing" checkbox in the Tools, Properties dialog in the VBA
editor.

If the circumstances I've described so far don't stymie you, then the
VBProject object has a VBComponents collection. Each VBComponent object has
a CodeModule object that is a property of the VBComponent. From there, the
ProcStartLine property gives you the location of a particular procedure in a
module, and the ProcCountLines tells you how many lines there are in the
procedure. other related properties and methods allow you to change the code
in the project.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Pat Hartman \(MVP\)]

Thanks for your explanation Jonathan.

I just found this article which has excellent Excel examples for what I want
to do with Word:

http://www.cpearson.com/excel/vbe.htm#ListModules

We want to have better programmatic control over these documents so I think
we'll just hire a temp to reset the macro security so I can manipulate the
documents with VBA from Access.

I guess I understand Microsoft's position and why "no access" would be the
default. But, I say it's time we take back the world from all the malicious
people and terrorists who have sprouted up in the past 20 years. I am sick
to death of being thwarted by security measures designed to deter the virus
writers. I am sick to death of taking my shoes off to go through airport
security and having to wait in long lines to have my personal belongings
searched by a complete stranger.

Thanks again

 

[Terry Hornsby]

There are three programmatic approaches you can try, using a wrapper to
batch update all the templates in your folder (the security options must be
set to allow access to vba project in all cases): -
1) identify the procedure in the module, delete it, add the new procedure if
necessary
2) identify the module, delete the whole module, import the new version
(assumes identically-named modules are actually identical, otherwise you
would need to manually standardise all your modules to begin, which wouldn't
be a bad thing)
3) Copy the text of the template to a new, blank template & import the new
module. You would need to ensure the new template has been set up with the
right styles, autotext entries etc (this can all be done programmatically
using a "master" template - this is the approach I've been using to update
and standardise my work's 350+ templates).

Another thought strikes me, however: -
If you only want the print macro to run when the template or document is
accessed from Access, open Word or excel invisibly. Have the print macro in
word or excel check to see if the application is visible. If it isn't, then
Word has been opened the normal way, & you can tell the code not to run the
print macro. If it's invisible, you can tell the print macro to run.

Yes, you still have to update all the macros in the templates the first time
around, but you will no longer have to worry about the print macro running
inappropriately.


| Thanks for your explanation Jonathan.
|
| I just found this article which has excellent Excel examples for what I
want
| to do with Word:
|
| http://www.cpearson.com/excel/vbe.htm#ListModules
|
| We want to have better programmatic control over these documents so I
think
| we'll just hire a temp to reset the macro security so I can manipulate the
| documents with VBA from Access.
|
| I guess I understand Microsoft's position and why "no access" would be the
| default. But, I say it's time we take back the world from all the
malicious
| people and terrorists who have sprouted up in the past 20 years. I am
sick
| to death of being thwarted by security measures designed to deter the
virus
| writers. I am sick to death of taking my shoes off to go through airport
| security and having to wait in long lines to have my personal belongings
| searched by a complete stranger.
|
| Thanks again
|
| | >
| > | >>I am an experienced Access VBA developer but the word object model has
| >>defeated me. I need to do three things.
| >> 1. Document the macros in all the word documents in a folder.
| >> 2. Delete a particular macro from those documents (hopefully it always
| >> has the same name).
| >> 3. During Automation from Access add the macro back in and have it run
if
| >> the document is printed.
| >>
| >> Background. The client currently maintains two libraries of documents.
| >> One is used with an excel application that automats word and fills
| >> bookmarks with data. The other library is used for all other purposes.
| >> Maintaining the separate libraries is quite an administrative burden
and
| >> the client would like to merge them. I am also replacing the Excel
| >> application with an Access application that performs similar functions.
| >> Data capture (and storage) and document printing after filling
bookmarked
| >> fields. When the documents are filled from the Access application, a
| >> macro that checks that all bookmarks have data before allowing printing
| >> needs to run if the user attempts to print the document. However, this
| >> macro is getting in the way of other uses of the documents, hence the
| >> request to delete the existing macro.
| >>
| >> So far I have been able to print the names of the code modules with:
| >> Application.VBE.VBProjects(1).VBComponents(i).Name
| >> But, I can't drill down to the procedures within. Modules and macros
are
| >> exposed in the Access object model but they don't seem to be in the
Word
| >> object model. Can anyone help?
| >
| > This is a difficult one. The problem here is that each template is a
| > separate project. You won't be able to get at the VBProject object at
all
| > unless (assuming you are using Office 2003) you have the "Trust access
to
| > the VB project" box checked in the Macro, Security dialog. That box can
| > only be checked by hand, not set through code. It is unchecked by
default.
| >
| > Even if the box is unchecked, the VBProject object of another template
| > will not be accessible to you if the author of the template has checked
| > the "Lock Project for viewing" checkbox in the Tools, Properties dialog
in
| > the VBA editor.
| >
| > If the circumstances I've described so far don't stymie you, then the
| > VBProject object has a VBComponents collection. Each VBComponent object
| > has a CodeModule object that is a property of the VBComponent. From
there,
| > the ProcStartLine property gives you the location of a particular
| > procedure in a module, and the ProcCountLines tells you how many lines
| > there are in the procedure. other related properties and methods allow
you
| > to change the code in the project.
| >
| >
| > --
| > Regards
| > Jonathan West - Word MVP
| > www.intelligentdocuments.co.uk
| > Please reply to the newsgroup
| > Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org
| >
| >
|
|