problem protecting word document by macro in excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

natanz - 02 Feb 2006 14:23 GMT

I keep a log of rfi's(requests for information) in excel. when i
answer them i open up a document from a template in word. i have been
working on automating this process. so far i have gotten to the point
where i can open a document in word, unprotect it, change some of it's
formfields and bookmarks. but now i am trying to reprotect it. So
here's my code:

Sub OpenTemplate(DocType As String) 'in this case it is "RFI Response"

Dim WordApp As Object
Set WordApp = Nothing
Dim worddoc As Object
Dim TemplateFile As String
Dim TemplateDir As String

'is this code doing what i want it to do, namely checking if there
is an open copy of MS
'word
On Error Resume Next
Set WordApp = GetObject("word.application")
If Err.Number <> 0 Then
Set WordApp = CreateObject("word.application")
End If
TemplateDir = "C:\Documents and Settings\My Documents\forms\"
TemplateFile = TemplateDir & DocType & ".dot"
WordApp.Documents.Add Template:=TemplateFile
Set worddoc = WordApp.activedocument

If DocType = "rfi response" Then
Call RFIInfo(worddoc)
End If

WordApp.Visible = True
End Sub

and then:

Public Sub RFIInfo(worddoc As Object)
worddoc.Unprotect
worddoc.Bookmarks("projname").Range.Text = _
ActiveWorkbook.Sheets("project info").Range("a7")
worddoc.Bookmarks("projnum").Range.Text = _
"FILE: " & ActiveWorkbook.Sheets("project
info").Range("b7")
With worddoc.FormFields("dropdown1").DropDown.ListEntries
.Add ("september")
.Add ("marching orders")
.Add ("Mr. Humboldt")
End With

'everything works exactly as i need until this last line:
worddoc.Protect Type:=wdAllowOnlyFormFields, noreset:=True
End Sub

so i think i may be doing a couple things wrong. I don't think i am
checking for an open copy of word exactly right, in my on error resume
next, but it is working.
i am also not sure i am passing the object variable worddoc with enough
information. Is it a problem that the variable worddoc has the same
name in both subs? I feel like it is not the same thing after it is
passed, maybe i should have a ByRef in there? Anyway, i am an
intermediate newbie at this, just kind of fumbling my way through. Any
suggestions about ways to make my code better, cleaner, more efficient,
etc. would be greatly appreciated.

Reply to this Message

Jay Freedman - 02 Feb 2006 15:28 GMT

Hi natanz,

Bearing in mind that I haven't loaded your code to try it, I think the
problem is probably caused by using the constant wdAllowOnlyFormFields
without first setting a reference to the Word library. I'll bet also that
you don't have Option Explicit at the top of your module. Together, those
two facts mean that VBA assumes the name wdAllowOnlyFormFields is an
undeclared Variant variable, which it initializes to the value 0. The
.Protect method interprets a value of 0 in the Type parameter to mean "allow
only revisions" instead of "allow only form fields" (which would be a value
of 2).

You can solve this either by setting the reference (click Tools > References
and set a reference to the Microsoft Word Object Library) or by declaring
the following in RFIInfo:

Const wdAllowOnlyFormFields = 2

Another possible problem is related to your declaring worddoc As Object. An
Object variable is completely generic, and it doesn't necessarily have
access to the methods and properties of the more specialized kinds of
objects derived from it.

First, if you haven't already done so, set the reference to the Word
library. Then change the declaration of worddoc to

Dim worddoc As Word.Document

Also change the first line of the subroutine to

Public Sub RFIInfo(worddoc As Word.Document)

A more philosophical consideration is that the RFIInfo routine doesn't do
anything to shorten your code; in fact, it slows it down a little because
there's overhead associated with setting up a subroutine call. You could
just take all the code from the routine and stuff it into the OpenTemplate
routine in place of the Call statement. In general, a subroutine makes sense
when either (a) the routine will be called repeatedly in a loop with
different parameter values or (b) you have two or more subroutines that do
different things, and you decide at runtime to call one or another on the
basis of supplied information.

As it is, the only reason to separate RFIInfo from the main routine is for
easier understanding by human readers; that is, all the code related to
manipulating the document is gethered into a separate chunk. This isn't a
bad thing, but it isn't strictly necessary.

On the plus side, your code to check for a running copy of Word or start a
new one is pretty good the way it is. You might want to look at the code at
http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm for some minor
improvements.

Signature

Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

> I keep a log of rfi's(requests for information) in excel. when i
> answer them i open up a document from a template in word. i have been
[quoted text clipped - 61 lines]
> through. Any suggestions about ways to make my code better, cleaner,
> more efficient, etc. would be greatly appreciated.

Reply to this Message

natanz - 02 Feb 2006 15:49 GMT

thank you very much. that is all really helpful, and makes a lot of
sense. I realize at this point that the RFIinfo sub could be rolled
into the opentemplate sub, but eventually i plan to reuse the
opentemplate to open other types of templates so this keeps it generic.
It seems like a useful thing that i am 90% sure i will use again
differently as this evolves.

Reply to this Message

Rate this thread: