Access data to Word-template stays empty

Feb 21, 2012
Reaction score
Hi Everyone,

I'm completely new to vba-coding so i hope that someone can help me.

I've made a Word-template with bookmarks.
Then in Access I've put a button in my form to open that Word-template and put in the relevant data.
I have several books on how to put the code together and every example says different things.
After many tries i've finally succeeded to open the word template with the code of the button but the template stays empty (data is not transferred).

I know that there's probably some code missing or just put in wrong but since i've got no experience and can't seem to find anything in these books i hope to find the answer here.

This is my VBA-code :

Private Sub Word_Click()

Dim Wrd As Word.Application
Dim strFactNr As String
Dim strFactDat As String

Set Wrd = CreateObject("Word.Application")

Dim MergeDoc As String
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc & "\BLANCOfactuurVDL.dotx"

Wrd.Documents.Add MergeDoc
Wrd.Visible = True

With Wrd.Selection
.GoTo what:=wdGoToBookmark, Name:="FactNr"
.TypeText Text:=strFactNr

.GoTo what:=wdGoToBookmark, Name:="FactDat"
.TypeText Text:=strFactDat

End With

End Sub
Any kind of help will be much appreciated.
Thanks in advance.





Feb 24, 2012
Reaction score
I have found that programmatically replacing the bookmarks in a Word doc template is more of a PITA than it's worth, especially since if you need a field to appear more than once in a document, you have to create separate (and distinctly named) bookmarks to handle this requirement....

The method I am currently using to generate Word Docs is to automate mail merge:

1. I created a custom class to handle the mail merge, so in access I just declare a new doc class instance, and set the .csv datasource property, and the template file name, and call the ExportDocument method to execute the mailmerge.

2. I have a function that creates a .csv data source for the mail merge using a SQL statement or a Query name (using DoCmd.TransferText method), and returns the dynamically created .csv file name, which I pass to the custom class above.

3. In your template you have to insert the properly named merge fields from your SQL or query.

Please keep in mind, I am no programmer... this is just what I pieced together from all kinds of different sources to accomplish what was needed... Hopefully you can adapt this to suit your needs!

Option Compare Database
Option Explicit
' *****************************************************
'   Name:           clsDocument
'   Created:        24 Jan 2012
'   Purpose:        Define data structure, elements and
'                   methods used to create and manipulate
'                   auto-generated documents
' *****************************************************
Dim pCSVFile As String
Dim pDocumentTemplate As String
Public Property Get DataSource() As String
    DataSource = pCSVFile
End Property
Public Property Let DataSource(ByVal sSQL As String)
    pCSVFile = ExportToCsvFile(sSQL)
End Property
Public Property Get DocumentTemplate() As String
    DocumentTemplate = pDocumentTemplate
End Property
Public Property Let DocumentTemplate(sDocumentTemplateID As String)
    Dim sDocumentTemplate As String
    sDocumentTemplate = ExtractDocumentFile(CLng(sDocumentTemplateID))
    pDocumentTemplate = sDocumentTemplate
End Property
Public Sub ExportDocument(ByVal docSaveFormat As WdSaveFormat)
    'On Error GoTo ErrHandler
    Dim oWord As Word.Application
    Dim oDoc As Word.Document
    Dim oMergedDoc As Word.Document
    Dim i As Integer, j As Integer
    Dim NewResult As String
    Dim blnCreated As Boolean
    Dim strExtension As String
    blnCreated = False
    'Make sure the CSVFile property is set
    If Nz(pCSVFile, vbNullString) = vbNullString Then
        MsgBox "Error: DataSource propery of Document Class must be set prior to executing ExportDocument method.", vbOKOnly, "clsDocument:ExportDocument"
        Exit Sub
    End If
    'Make sure the DocumentTemplate is set
    If Nz(pDocumentTemplate, vbNullString) = vbNullString Then
        MsgBox "Error: DocumentTemplate propery of Document Class must be set prior to executing ExportDocument method.", vbOKOnly, "clsDocument:ExportDocument"
        Exit Sub
    End If
    Set oWord = New Word.Application
    Set oDoc = oWord.Documents.Add(pDocumentTemplate)
    'attach temp csv file to mail merge document template; execute mail merge
    With oDoc.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:=pCSVFile, LinkToSource:=False, Connection:="", SQLStatement:="", ConfirmConversions:=False
        .Destination = wdSendToNewDocument
    End With
    'save merged document in Word and PDF format
    Set oMergedDoc = oWord.ActiveDocument
    If docSaveFormat = wdFormatPDF Then
        strExtension = ".pdf"
        strExtension = ".docx"
    End If
    sOutputDocumentFile = SaveFile(Environ("USERPROFILE") & "\" & oMergedDoc.FullName & strExtension, oDoc.Name)
    If Nz(sOutputDocumentFile, vbNullString) = vbNullString Then
        MsgBox "Cancelled."
        GoTo ExitClass
    End If
    If docSaveFormat = wdFormatPDF Then 'PDF
        oMergedDoc.ExportAsFixedFormat OutputFileName:=sOutputDocumentFile, ExportFormat:= _
            wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _
            wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
            Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
            CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
            BitmapMissingFonts:=True, UseISO19005_1:=False
        blnCreated = True
    Else    'Word Doc
        oMergedDoc.SaveAs sOutputDocumentFile, FileFormat:=docSaveFormat
        blnCreated = True
    End If
    'Kill our instance of Word, then shut down and clean up ;)
    On Error Resume Next
    oWord.Quit False
    Set oWord = Nothing
    Set oDoc = Nothing
    Set oMergedDoc = Nothing
   ' If FileExist(pCSVFile) Then Kill (pCSVFile)
    If FileExist(pDocumentTemplate) Then Kill (pDocumentTemplate)
    If blnCreated Then
        If MsgBox("Document creation is complete. Would you like to view the file now?", vbYesNo, _
          "View created document") = vbYes Then
            OpenThisFile (sOutputDocumentFile)
        End If
    End If
    Exit Sub
    MsgBox "Unhandled Error: " & Err.Description
    If Err.Number = 429 Then
        'If we got an error, that means there was no Word Instance
        Set oWord = New Word.Application
    End If
    'Reset Error Handler
    On Error GoTo 0
End Sub
Private Function ExportToCsvFile(strQueryNameOrSQL As String) As String
On Error GoTo Err_Handler
    Dim db As DAO.Database
    Dim strTimestamp As String
    Dim strQdfName As String
    Dim tempTable As String
    Dim tempDirectory As String
    Dim ExportFileName As String
    Dim qdf As DAO.QueryDef
    strTimestamp = Format(Now(), "HhNnSs-yyyymmdd")
    strQdfName = "~temp" & strTimestamp
    Dim qr As DAO.QueryDef
    Dim QueryName As String
    QueryName = strQueryNameOrSQL
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(strQdfName, strQueryNameOrSQL)
    tempTable = "~temptbl" & strTimestamp
    tempDirectory = Environ("USERPROFILE")
    ExportFileName = tempDirectory & "\" & strTimestamp & ".csv"
    DoCmd.TransferText acExportDelim, , strQdfName, ExportFileName, True
    DoCmd.DeleteObject acQuery, strQdfName
    ExportToCsvFile = ExportFileName
    Exit Function
    MsgBox Err.Description
    Resume exit_function
End Function
Private Function FileExist(strFile As String) As Boolean
On Error GoTo Err_Handler
    FileExist = False
    If Len(Dir(strFile)) > 0 Then
        FileExist = True
    End If
    Exit Function
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FileExist" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occured!"
    GoTo Exit_Err_Handler
End Function


  • clsDocument.txt
    6.2 KB · Views: 266

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question