Delete macros from 1000 documents

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pat Hartman (MVP) - 02 Apr 2007 17:29 GMT

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?

Reply to this Message

Jonathan West - 03 Apr 2007 00:35 GMT

>I am an experienced Access VBA developer but the word object model has
>defeated me. I need to do three things.
[quoted text clipped - 22 lines]
> 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.

Signature

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

Reply to this Message

Pat Hartman (MVP) - 03 Apr 2007 18:26 GMT

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.
[quoted text clipped - 41 lines]
> there are in the procedure. other related properties and methods allow you
> to change the code in the project.

Reply to this Message

Terry Hornsby - 03 Apr 2007 20:40 GMT

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.
|
[quoted text clipped - 62 lines]
| > there are in the procedure. other related properties and methods allow you
| > to change the code in the project.

Reply to this Message