Access data to Word-template stays empty


Joined
Feb 21, 2012
Messages
5
Reaction score
0
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 :

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.

Greetzzz,
Bieke
 
Ad

Advertisements

Ad

Advertisements

Joined
Feb 24, 2012
Messages
1
Reaction score
0
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!

Cheers!
Eric
Code:
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
        .Execute
    End With
 
    'save merged document in Word and PDF format
    Set oMergedDoc = oWord.ActiveDocument
 
    If docSaveFormat = wdFormatPDF Then
        strExtension = ".pdf"
    Else
        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
 
 
 
 
ExitClass:
    '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
ErrHandler:
 
    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:
    Exit Function
Err_Handler:
    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_Err_Handler:
    Exit Function
 
Err_Handler:
    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
 

Attachments

  • 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

Top