Automating Word from Access

[Pat Hartman \(MVP\)]

I have a macro that I want to add to a document when I open it from Access.
The macro will be attached to the print event and will prevent printing if
any formfield is not filled. This macro used to reside in all the documents
but it caused a problem for one set of users so they want it removed. The
only time the macro is required, is if the user uses my application to
populate the document fields. We want to make sure that they do not print
the document if any data is missing.

I have done a fair amount of automation but nothing like this. Can anyone
provide code to add this macro using VBA from my Access application? Or,
perhaps a link to a relevant web site. I can't seem to find anything that
uses automation to do this. There is very little help on Word automation
from other applications and this isn't something you are likely to want to
do within a Word doc.
Thanks,
Pat

 

[Paul Shapiro]

Here's a vba routine that inserts code into a Word document. Tested with
Word 2003, and apparently back to Word 97 from one of the comments, but I
don't remember. The error handling uses custom objects, but can be replaced
with msgBox or whatever you use. I add the desired code as VBA in a new
module in the Word document. I think it shows up in the list of macros that
can be run. In your case you are probably looking to also set the function
as an event handler, which isn't in this code.

mobjWord is a module-wide (in the Access module where this code is running)
Word application object which is already open when this code is called.
Paul Shapiro


Private Function WordDocumentAddModuleAndCode() As Boolean
On Error GoTo ErrorHandler
Dim oWordModule As Object, oComponent As Object
Dim fSuccess As Boolean
Dim strCode As String, strMsg As String

'Build a string with the code we want to insert.
'String is "too complex" to be done as a string constant
strCode = _
"Sub UpdatePageNumbersInTextboxesIndexAndOtherFields()" & vbCrLf _
& "On Error GoTo ErrorHandler" & vbCrLf _
& " Dim oStoryRange As Word.Range" & vbCrLf & vbCrLf _
& " 'First update the page numbers in text boxes" & vbCrLf _
& " ActiveDocument.StoryRanges(wdTextFrameStory).Fields.Update" & vbCrLf
& vbCrLf _
& " 'Now update all fields in all story ranges, _" & vbCrLf _
& " which should include the author index" & vbCrLf _
& " For Each oStoryRange In ActiveDocument.StoryRanges" & vbCrLf _
& " oStoryRange.Fields.Update" & vbCrLf & vbCrLf _
& " 'Process any other story ranges of the current type" & vbCrLf _
& " While Not (oStoryRange.NextStoryRange Is Nothing)" & vbCrLf _
& " Set oStoryRange = oStoryRange.NextStoryRange" & vbCrLf _
& " oStoryRange.Fields.Update" & vbCrLf _
& " Wend" & vbCrLf _
& " Next oStoryRange" & vbCrLf & vbCrLf

strCode = strCode _
& " '2 new sections were added by the index table:" & vbCrLf _
& " ' one for the index and one to revert to a zero-column format." &
vbCrLf _
& " ' We want to set dividing lines for the next-to-last section." &
vbCrLf _
& " With ActiveDocument.Sections(ActiveDocument.Sections.Count -
1).PageSetup.TextColumns" & vbCrLf _
& " If .Count > 1 Then" & vbCrLf _
& " .LineBetween = True" & vbCrLf _
& " .Spacing = 0.1 * 72" & vbCrLf _
& " End If" & vbCrLf _
& " End With" & vbCrLf & vbCrLf _

strCode = strCode _
& "ExitHandler:" & vbCrLf _
& " Exit Sub" & vbCrLf & vbCrLf _
& "ErrorHandler:" & vbCrLf _
& " MsgBox ""MSWord Error updating page numbers, index and other
fields:"" _" & vbCrLf _
& " & vbCrLf & Err.Description, vbExclamation, ""Error #"" &
Err.Number" & vbCrLf _
& " Resume ExitHandler" & vbCrLf _
& " Resume" & vbCrLf _
& "End Sub" & vbCrLf

Set oComponent = mobjWord.ActiveDocument.VBProject.VBComponents _
.Add(1) 'Add a standard module
oComponent.Name = "BroadwayData"

Set oWordModule = oComponent.CodeModule
oWordModule.AddFromString strCode

'Assume this worked
fSuccess = True

ExitHandler:
On Error Resume Next
WordDocumentAddModuleAndCode = fSuccess
Exit Function

ErrorHandler:
Select Case Err.Number
Case 6068 'Programmatic access to Visual Basic Project is not trusted.
_
This only applies to Word 2K and up.
mobjWord.Dialogs.Item(1361).Show 'wdDialogToolsOptionsSecurity
(constant not defined in Word97)
strMsg = "Word is not allowing this program to write the necessary
code into the Word document. " _
& "Bring up the currently open Word program, go to the Tools Menu,
Options item. " _
& "On the Security tab, click the Macro Security button. " _
& "On the Trusted Sources tab, check Trust access to Visual Basic
Project. " _
& "You should remember to turn this back off after the document is
finished. " & vbCrLf & vbCrLf _
& "Click OK here to continue once you have said OK to all of Word's
prompts."
If vbOK = MsgBox(strMsg, vbInformation + vbOKCancel, "You need to
modify a Word security setting") Then
Resume 'try again
Else
Set objError = New pjsError
objError.pjsErrorCode Procedure:="WordDocumentAddModuleAndCode",
ModuleName:=mconStrModuleName
Set objError = Nothing
fSuccess = False
End If
Case Else
Set objError = New pjsError
objError.pjsErrorCode Procedure:="WordDocumentAddModuleAndCode",
ModuleName:=mconStrModuleName
Set objError = Nothing
fSuccess = False
End Select

Resume ExitHandler
Resume
End Function

 

[Susie DBA [MSFT]]

why would you use word dude

require certain fields to be entered, use an Access report

 

[Pat Hartman \(MVP\)]

Thanks Paul, I'll give it a whirl.

Here's a vba routine that inserts code into a Word document. Tested with
Word 2003, and apparently back to Word 97 from one of the comments, but I
don't remember. The error handling uses custom objects, but can be
replaced with msgBox or whatever you use. I add the desired code as VBA in
a new module in the Word document. I think it shows up in the list of
macros that can be run. In your case you are probably looking to also set
the function as an event handler, which isn't in this code.

mobjWord is a module-wide (in the Access module where this code is
running) Word application object which is already open when this code is
called.
Paul Shapiro


Private Function WordDocumentAddModuleAndCode() As Boolean
On Error GoTo ErrorHandler
Dim oWordModule As Object, oComponent As Object
Dim fSuccess As Boolean
Dim strCode As String, strMsg As String

'Build a string with the code we want to insert.
'String is "too complex" to be done as a string constant
strCode = _
"Sub UpdatePageNumbersInTextboxesIndexAndOtherFields()" & vbCrLf _
& "On Error GoTo ErrorHandler" & vbCrLf _
& " Dim oStoryRange As Word.Range" & vbCrLf & vbCrLf _
& " 'First update the page numbers in text boxes" & vbCrLf _
& " ActiveDocument.StoryRanges(wdTextFrameStory).Fields.Update" &
vbCrLf & vbCrLf _
& " 'Now update all fields in all story ranges, _" & vbCrLf _
& " which should include the author index" & vbCrLf _
& " For Each oStoryRange In ActiveDocument.StoryRanges" & vbCrLf _
& " oStoryRange.Fields.Update" & vbCrLf & vbCrLf _
& " 'Process any other story ranges of the current type" & vbCrLf _
& " While Not (oStoryRange.NextStoryRange Is Nothing)" & vbCrLf _
& " Set oStoryRange = oStoryRange.NextStoryRange" & vbCrLf _
& " oStoryRange.Fields.Update" & vbCrLf _
& " Wend" & vbCrLf _
& " Next oStoryRange" & vbCrLf & vbCrLf

strCode = strCode _
& " '2 new sections were added by the index table:" & vbCrLf _
& " ' one for the index and one to revert to a zero-column format." &
vbCrLf _
& " ' We want to set dividing lines for the next-to-last section." &
vbCrLf _
& " With ActiveDocument.Sections(ActiveDocument.Sections.Count -
1).PageSetup.TextColumns" & vbCrLf _
& " If .Count > 1 Then" & vbCrLf _
& " .LineBetween = True" & vbCrLf _
& " .Spacing = 0.1 * 72" & vbCrLf _
& " End If" & vbCrLf _
& " End With" & vbCrLf & vbCrLf _

strCode = strCode _
& "ExitHandler:" & vbCrLf _
& " Exit Sub" & vbCrLf & vbCrLf _
& "ErrorHandler:" & vbCrLf _
& " MsgBox ""MSWord Error updating page numbers, index and other
fields:"" _" & vbCrLf _
& " & vbCrLf & Err.Description, vbExclamation, ""Error #"" &
Err.Number" & vbCrLf _
& " Resume ExitHandler" & vbCrLf _
& " Resume" & vbCrLf _
& "End Sub" & vbCrLf

Set oComponent = mobjWord.ActiveDocument.VBProject.VBComponents _
.Add(1) 'Add a standard module
oComponent.Name = "BroadwayData"

Set oWordModule = oComponent.CodeModule
oWordModule.AddFromString strCode

'Assume this worked
fSuccess = True

ExitHandler:
On Error Resume Next
WordDocumentAddModuleAndCode = fSuccess
Exit Function

ErrorHandler:
Select Case Err.Number
Case 6068 'Programmatic access to Visual Basic Project is not
trusted. _
This only applies to Word 2K and up.
mobjWord.Dialogs.Item(1361).Show 'wdDialogToolsOptionsSecurity
(constant not defined in Word97)
strMsg = "Word is not allowing this program to write the necessary
code into the Word document. " _
& "Bring up the currently open Word program, go to the Tools Menu,
Options item. " _
& "On the Security tab, click the Macro Security button. " _
& "On the Trusted Sources tab, check Trust access to Visual Basic
Project. " _
& "You should remember to turn this back off after the document is
finished. " & vbCrLf & vbCrLf _
& "Click OK here to continue once you have said OK to all of
Word's prompts."
If vbOK = MsgBox(strMsg, vbInformation + vbOKCancel, "You need to
modify a Word security setting") Then
Resume 'try again
Else
Set objError = New pjsError
objError.pjsErrorCode
Procedure:="WordDocumentAddModuleAndCode", ModuleName:=mconStrModuleName
Set objError = Nothing
fSuccess = False
End If
Case Else
Set objError = New pjsError
objError.pjsErrorCode Procedure:="WordDocumentAddModuleAndCode",
ModuleName:=mconStrModuleName
Set objError = Nothing
fSuccess = False
End Select

Resume ExitHandler
Resume
End Function

 

[Tony Toews [MVP]]

why would you use word dude

require certain fields to be entered, use an Access report

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm