Data Control, LLC

Recently we’ve been asked by a number of clients including Roofing Consulting Services (Consultants, Richmond, VA), KnoxMarine (Surveyors, Charleston, SC) and Hirschler Fleischer (Attorney, Richmond, VA) to integrate Access with Word. What these companies have in common is that they produce reports to their clients and they want to both standardize the reports across their company and save their report writers time in producing these reports. A side benefit is that all of the necessary data to create the report is now consolidated in the Access Database. Lastly, because we are using Access to determine where Word Template files are held and where the destination reports go (and how they are named), Access is actually functioning as a light document management tool for the entire report creation and management process.

We accomplish this in Access by using a combination of file system objects (you’ll need to register the library in the access modules) and the Word Object Library (you’ll also need to register this.

We extensively use bookmarks throughout the various Word documents and create .doc or .docx ‘template’ files. These are NOT the Word Template files. They are standard word document files we name ‘Report 1 TEMPLATE.docx’ for example. If the user wants to change their template files, they simply open them and change them, taking care not to bother the highlighted bookmarks.

Here’s an example of extensive bookmarking in a Word document that Access will use to fill in data:

Access Database vba automation of Word through bookmarks

We then create code in Access to copy the template file and name the destination document and place the document where it belongs, often in a file folder with a client id number. Then we scroll through the document replacing the bookmarks with either text from the access database or images from the Access database (actually, Access stores the path and file of the image, not the image itself). Here’s an example of this code…

Function fncLetterToMerge(pLetterToMerge As String, id As Long)

‘pass in a field name in zstblinformation which represents the path to the file to be created

On Error GoTo c12_err

Dim rs As Recordset
Dim strPathAndFileToTemplate As String
Dim strPathAndFileToCreate As String
Dim xENCLOSUREx As String
Dim xGeneralNotesx As String
Dim intAssignedToID As Integer

DoCmd.RunCommand acCmdSaveRecord

If IsNull(DLookup(“PathToReports”, “jobs”, “id = ” & id)) Then
MsgBox “Please supply a path to reports for this file”
Exit Function
End If

‘strPath = DLookup(“defaultexportfolder”, “zstblInformation”)
strPathAndFileToTemplate = DLookup(“defaulttemplatefolder”, “zstblInformation”) & “\” & pLetterToMerge
strPathAndFileToCreate = DLookup(“PathToReports”, “jobs”, “id = ” & id) & “\” & DLookup(“[Job Number]”, “Jobs”, “id = ” & id) & ” – ” & pLetterToMerge

If Right(strPathAndFileToCreate, 4) = “docx” Then
strPathAndFileToCreate = Left(strPathAndFileToCreate, Len(strPathAndFileToCreate) – 14) & “.docx”
Else
strPathAndFileToCreate = Left(strPathAndFileToCreate, Len(strPathAndFileToCreate) – 13) & “.doc”
End If

‘Select Case pLetterToMerge
‘Case Is = “FileNotesMrg”

‘End Select

If FileExists(strPathAndFileToCreate) Then
intReply = MsgBox(“This file already exists. Do you wish to delete and replace with a new version?”, vbOKCancel, “Warning”)
If intReply = 1 Then
Kill strPathAndFileToCreate
Else
Exit Function
End If
End If

intAssignedToID = DLookup(“idtbl_employees”, “tbl_Employees”, “initials = ‘” & DLookup(“[assgn to]”, “jobs”, “id = ” & id) & “‘”)

BuildTemplate:
Dim oWord As Object ‘Word.Application
Dim doc As Object ‘Word.Document
Dim objShapes

Dim xFoundx As String
Dim xRecommendx As String

Set oWord = CreateObject(“Word.Application”)
Set doc = oWord.Documents.Open(strPathAndFileToTemplate)
oWord.Visible = True

doc.SaveAs (strPathAndFileToCreate)

With oWord.ActiveDocument

Select Case pLetterToMerge
Case Is = “XXXXX”

Case Is = “KMSC Photo Sheet – TEMPLATE.docx”
If IsNull(DLookup(“datephotostaken”, “jobs”, “id = ” & [id])) Then
MsgBox “Please enter the date the photos were taken on the main form”
Exit Function
End If

.Bookmarks(“xxClaimNumberxx”).Select
If Not IsNull(DLookup(“[claim number]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[claim number]”, “Jobs”, “id = ” & id)

.Bookmarks(“xxJobNumberxx”).Select
If Not IsNull(DLookup(“[Job Number]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[Job Number]”, “Jobs”, “id = ” & id)

.Bookmarks(“xDatex”).Select
If Not IsNull(DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)

.Bookmarks(“xDatex”).Select
If Not IsNull(DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“[datephotostaken]”, “Jobs”, “id = ” & id)

‘i = 0
‘Set rs = CurrentDb.OpenRecordset(“select * from trptPhotoSheet where id = ” & id)
‘Do Until rs.EOF
‘ i = i + 1

‘If Not IsNull(rs!PhotoPathAndFile) Then .Bookmarks(“xxPhoto” & i & “xx”).Range.InlineShapes.AddPicture FileName:=rs!PhotoPathAndFile
‘ .Bookmarks(“xxPhoto” & i & “xx”).Select

‘If Not IsNull(rs!PhotoCaption) Then oWord.Selection.Text = rs!PhotoCaption
‘ rs.MoveNext
‘Loop

For i = 1 To 10
If Not IsNull(DLookup(“photo” & i, “Jobs”, “id = ” & id)) Then
.Bookmarks(“xxPhoto” & i & “xx”).Range.InlineShapes.AddPicture FileName:=DLookup(“photo” & i, “Jobs”, “id = ” & id)
‘.Bookmarks(“xxPhoto” & i & “xx”).Select
.Bookmarks(“xxPhotoCaption” & i & “xx”).Select
If Not IsNull(DLookup(“photocaption” & i, “Jobs”, “id = ” & id)) Then oWord.Selection.Text = DLookup(“photocaption” & i, “Jobs”, “id = ” & id)
End If
Next