View Full Version : Access to Word Merged Document

2012-04-18, 06:40 PM
Hi, I am using an MSAccess database to automatically merge data into a Word Template, using the code below. Unfortunately, The final "merged" document still links back to the MSaccess database, instead of creating a separate instance of the document. Can anyone help me figure this out?

Private Sub Copy08Customer_Click()

[DateToday] = Date
[LabelNumber] = "-02"
[LabelName] = "Customer Image"
[LabelFileName] = [ComponentPartNoStripped] & "-08_Customer_CD.doc"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull([-08_Labels_Directory_Location]) Then
MsgBox ("Error: The -08 Directory does not exist.")
Exit Sub
End If
If Not DirExists([-08_Labels_Directory_Location]) Then
MsgBox ("Error: The -08 Directory does not exist.")
Exit Sub
End If
If IsNull([OS Type]) Then
MsgBox ("The Operating system type is not Specified.")
Exit Sub
End If
'If IsNull([Contract Number]) Then
' MsgBox ("The Contract/TAA number is not Specified.")
' Exit Sub
'End If
On Error GoTo ErrorHandler

Dim appWord As Word.Application
Dim docs As Word.Documents
Dim strLetter As String
Dim prps As Object
Dim strDate As String
Dim fso As New Scripting.FileSystemObject
Dim fil As Scripting.file
Dim strTemplate As String
Dim strTemplatePath As String
Dim strTemplateNameAndPath As String
Dim doc As Word.Document
Dim strTitle As String
Dim strPrompt As String
Dim DocPath As String
Dim CurrentDate As String

CurrentDate = Date

Set appWord = GetObject(, "Word.Application")
strTemplatePath = "\\server\WordTemplates (file://\\server\WordTemplates)"

If [Project].Value = "COTS" Then
strLetter = "Template-08_Customer_CD_COTS.dot"
strLetter = "Template-08_Customer_CD.dot"
End If

strTemplateNameAndPath = strTemplatePath & "\" & strLetter
''Debug.Print "Template and path: " & strTemplateNameAndPath

'Set path and filename
[-08_Customer_CD_Filename] = [-08_Labels_Directory_Location] & "\" & [LabelFileName]
DocPath = [-08_Customer_CD_Filename].Value
'Debug.Print "Document Path: " & DocPath

Set docs = appWord.Documents
Set doc = docs.Add(strTemplateNameAndPath)

Set prps = doc.CustomDocumentProperties

With prps
.Item("Project").Value = Nz(Me![Project])
.Item("Component").Value = Nz(Me![Component])
.Item("ComponentPartNoStripped").Value = Nz(Me![ComponentPartNoStripped])
.Item("Revision").Value = Nz(Me![Revision])
.Item("Product").Value = Nz(Me![Product])
.Item("CurrentDate").Value = Nz(Me![CurrentDate])
.Item("PVCS Project Label").Value = Nz(Me![PVCS Project Label])
.Item("CurrentDate").Value = Nz(Me![CurrentDate])
.Item("Contract Number").Value = Nz(Me![Contract Number])
.Item("OS Type").Value = Nz(Me![OS Type])
End With

With appWord
.Visible = False
.Selection.MoveDown Unit:=wdLine, Count:=1
.WindowState = wdWindowStateMinimize
End With

'Save the document
doc.SaveAs DocPath

'Close the document and clear the variables
docs.Close SaveChanges:=wdDoNotSaveChanges
Set docs = Nothing
Set appWord = Nothing

Exit Sub

If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application")
Resume Next
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

End Sub

2012-05-22, 09:35 PM
Can anyone recommend a way to do this?

Ken Puls
2012-05-22, 10:41 PM
Ah, you're changing the DocumentProperties... they don't print. :)

Try using Word's Bookmarks. They're like MergeFields, only a little different. You can't see them once they're in your document, but you can insert them with names that you like. (I'd suggest making those the same names as your Access fields.) Then you'd refer to them as:

doc.Bookmarks("Project").Range.Text = Nz(Me![Project])

Hope that helps,