Automation between Word and Access

[dkellygb]

I am struggling with automation with office XP.

I have an Access database where every time a user communicates with a customer, it is logged with the date and time. I am trying to create a facility where the user can select from a list of about 5 word templates to create a letter to a customer and embed that letter in a bound object frame. When the letter is created, the appropriate address information should be put into the word document. I have been able to accomplish that using this code:

Dim objWord As Word.Application

Set objWord = New Word.Application
objWord.Visible = True
objWord.Documents.Add Application.CurrentProject.Path _
& "\SalesLetter.dot"

fname = "FullName from database"
Add = "address from database"
sal = "salutation from database"

With objWord.ActiveDocument.Bookmarks
.Item("FullName").Range.Text = fname
.Item("FullAddress").Range.Text = Add
.Item("Salutation").Range.Text = sal
End With

This creates a document and inserts the appropriate data.

I have also been able to figure out how to create an embedded word document:

With Me!Obj
.Enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLEEmbedded
' Class statement for Word document.
.Class = "Word.Document"
' Specify the file to be embedded.
' Type the correct path name.
.SourceDoc = Application.CurrentProject.Path & "\SalesLetter.doc"
' Create the embedded object.
.Action = acOLECreateEmbed
End With

But in creating this document, I haven’t been able to determine how refer to the document to execute the code to add address information.

I can see two approaches:

1. Figure out how to add the document I created using automation in the first code snippet to a bound object frame.
2. Figure out how to refer to the document I have created in the bound object frame using automation

Does anyone have any suggestions or alternative approaches?

 

[Axoliien]

To get the object you are wanting, just create an instance of a Word.Document object and set it equal to your initialization of your new Word.Document in the code above.

Example:

'---------------------------------------------------------------------------------------
' Procedure : SetWordObject
' Date      : 01/12/2005
' Author    : tdandrea
' Purpose   : Sets the object WApp to a valid MS Word Application by first trying to
'             reference an open instance.  If there is no open instance, then a new
'             instance is created and referenced.
'---------------------------------------------------------------------------------------
'
Public Function SetWordObject(ByRef WApp As Word.Application) As Boolean
    Dim NewWordOpen As Boolean 'Determines if the application was already open, or if we create a new instance.
    NewWordOpen = True
On Error Resume Next
    Set WApp = GetObject(, "Word.Application")
    If Err.Number = 429 Then 'There is no open Word Application available for reference.  Create a new instance.
        Set WApp = New Word.Application
        Err = 0
    End If
    
On Error GoTo ExitWordErr
    WApp.Visible = True
    WApp.Activate
    SetWordObject = True
    Exit Function
    
ExitWordErr: 'There was an error while referencing the Application object.
    MsgBox "There was an error while opening Microsoft Word.  The application has terminated." & vbCrLf & _
           "Error Generated: " & Err.Number & vbCrLf & _
           "System Description: " & Err.Description, vbCritical + vbOKOnly, "Cannot Open Microsoft Word"
    If Not WApp Is Nothing Then
        If NewWordOpen Then
            WApp.Quit wdDoNotSaveChanges
        End If
        Set WApp = Nothing
    End If
    SetWordObject = False
    Exit Function
End Function

Private Sub DoMailMerge()
    Dim WApp As Word.Application
    Dim WDoc as Word.Document
    
    On Error GoTo Err_MailMerge
    If Not SetWordObject Then Exit Sub
    Set WDoc = WApp.Documents.Open(TemplatePath, Visible:=False)
    'Merge and everything you want to do here
    '
    '
    '
    With WDOc
        .MailMerge.Execute
        .Close wdDoNotSaveChanges
    End With

ClearMem:
    On Error Resume Next
    Set WDoc = Nothing
    Set WApp = Nothing
    Exit Sub

Err_MailMerge:
    MsgBox "There was an error opening the template, vbOkOnly + vbInformation, "Error: Cannot Open Template"
    GoTo ClearMem
End Sub

 

[dkellygb]

Thank you for your advice. I was struggling with adding the document to a bound object frame. However, I think I found a solution.

On Error GoTo NoOleObject

Dim ctl As Control
Dim wrd As Word.Application
Dim fname As String
Dim sal As String
Dim add As String


Me!Obj.Verb = acOLEVerbOpen
Me!Obj.Action = acOLEActivate

Exit Sub

NoOleObject:
If Err = 2684 Then


Set ctl = Me!Obj
With Me!Obj
.Enabled = True
.Locked = False
' Specify what kind of object can appear in the field.
.OLETypeAllowed = acOLEEmbedded
' Class statement for Word document.
.Class = "Word.Document"
' Specify the file to be embedded.
' Type the correct path name.
.SourceDoc = Application.CurrentProject.Path & "\Sales Letter.doc"
' Create the embedded object.
.Action = acOLECreateEmbed
.Action = acOLEActivate
End With

Set wrd = ctl.Object.Application

fname = Me.Parent!ContSal & " " & Me.Parent!ContFname & " " & Me.Parent!ContSurName
add = Me.Parent!Company & vbCrLf & Me.Parent!CoAddress & vbCrLf & Me.Parent!PostCode
sal = Me.Parent!LetterSalutation

With wrd.ActiveDocument.Bookmarks
.Item("FullName").Range.Text = fname
.Item("FullAddress").Range.Text = add
.Item("Salutation").Range.Text = sal
End With

Else
MsgBox Error
End If

End Sub

 

[VBslammer]

One thing that can make OLE coding easier it to prefix your constants with [tt]"Access.Constants.acOLE..."[/tt] so you get Intellisense support. All of the OLE constants beginning with [tt]acOLE[/tt] can be used with ObjectFrames:

Me!oleControl.Action = Access.Constants.acOLECreateLink

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[dkellygb]

Thanks!

That's a good tip.

Dave

 

[ptpaton]

dkellygb,

Another option you could consider is inserting database fields into your Word templates, with the bookmark name something memorable. You may then refer to those bookmarks through VBA with the FormFields() property:

Where objWordDocument is set as the active document,

objWordDocument.FormFields("BookmarkName").Result = strYourString

On a side note, when you link your report, have you found that if you select "Icon" as the display type, the filename is replaced with the word "Document" instead of the file name? I'm trying to find a way around this...


-Patrick

Nine times out of ten, the simplest solution is the best one.