Access and Ms Word Integration (1 Viewer)

Fran Lombard

Registered User.
Local time
Today, 08:22
Joined
Mar 12, 2014
Messages
132
Hi Guys
It's been awhile since I've posted here, but thank you for all the previous help. My system has come a long way.

To the point. Now I would like to add some Access - Word integration to my system to generate contracts which will contain some info from the database.

Customer Name and Address
Project Information
Detail project Tasks

Does anyone have or can point me to some code examples of populating a word template with access data.

Thanks in advance
Fran
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:22
Joined
Oct 29, 2018
Messages
21,358
Hi. Try doing a search on "mail merge" tooics.

Sent from phone...
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:22
Joined
Feb 28, 2001
Messages
27,001
You might look up MailMerge as a specific topic. That is the easiest way. It is possible to do what it seems that you wanted to do, but will take some serious work if MailMerge isn't your answer.

Doing it from scratch, you might look at this thread:

I don't have a handy reference to the third way, which is halfway between the merge and ab initio methods; therefore I'll just summarize it. You can define a template file that contains pre-defined bookmarks at your critical locations, then copy this template to a file with a new name. Open the NEW file (the copied file), jump to the bookmarks one at a time in whatever order is required, and insert text after each one. Save and close. Word will automagically "close up" the justification and spacing for you.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:22
Joined
May 21, 2018
Messages
8,463
I have provided some detailed code for working with word in both directions.
Access to Word
Word to Access

My examples are for when you need something more robust then a simple mail merge, which is pretty limited.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:22
Joined
Feb 19, 2002
Messages
42,981
I don't use Mail Merge. I've attached a sample database that shows you simple OLE automation, including how to insert a "table" into the document so you can produce something like an Invoice if you need to. Mail Merge can't do that. I have applications that manage thousands of documents. Obviously, you can't do that with hard coding as this example does. If you need to manage multiple documents, you need to use a minimum of three tables.
1. Documents. Contains docName, QueryName,etc.
2. Fields. Contains FieldName, other more sophisticated options if necessary
3. DocFields. DocID, FieldID, BookmarkName
 

Attachments

  • SampleLettersOnly.zip
    33.7 KB · Views: 221
  • SampleWordAutomation_20210314.zip
    306.1 KB · Views: 223

Fran Lombard

Registered User.
Local time
Today, 08:22
Joined
Mar 12, 2014
Messages
132
Thanks for the all the input

I will look over each option and see what i can do.

Fran
 

561414

Active member
Local time
Today, 06:22
Joined
May 28, 2021
Messages
280
I've been using this code to populate templates for a while:

Code:
Function templateReplacer( _
                    querydef_name As String, _
                    where_clauses As String, _
                    template_path As String, _
                    desired_filename As String) _
                    As String

    ' This function has the purpose of filling .docx templates using a recordset
    ' from a ms access query definition, as an alternative for mail merge.
    '
    ' It outputs a the location of the resulting file as a string,
    ' appending date and time to the filename to (try to) avoid duplicates
    ' and saving it in the temp folder of Windows.
    '
    ' querydef_name: name of a query definition in the current db
    ' where_clauses: where clauses
    ' template_path: full path to the template docx
    ' desired_filename: how you want to call your resulting .docx, without path and extension
    '
    ' To use it in word documents with docx extension, just add the fields to the template like this {{field}}
    ' The fields you specify in the template must have the same name as those in the query definition

    On Error GoTo errHandler
    Dim sql1 As String
    sql1 = CurrentDb.QueryDefs(querydef_name).sql

    Dim sql2 As String
    sql2 = Replace(sql1, ";", " WHERE " & where_clauses)
    Debug.Print sql2
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset(sql2)

    Dim oWord As Object
    Set oWord = CreateObject("Word.Application")

    Dim oDoc As Object
    Set oDoc = oWord.Documents.Open(template_path)


'   |------------------- REPLACE {{FIELDS}} -------------------|
    Dim field As Variant

    Dim emptyFields As String
    emptyFields = ""

    Dim oStory As Object
    For Each field In rs.Fields
        Debug.Print field.Name, field.Value
        For Each oStory In oDoc.StoryRanges
            Do
                With oStory.Find
                    If Len(field) > 255 Then
                        ClipBoard_SetData field.Value
                        .Execute FindText:="{{" & field.Name & "}}", ReplaceWith:="^c", _
                        Format:=True, Replace:=2
                    Else
                        .Execute FindText:="{{" & field.Name & "}}", ReplaceWith:=Nz(field, ""), _
                        Format:=True, Replace:=2
                    End If
                End With
            Set oStory = oStory.Next
            Loop Until oStory Is Nothing
        Next oStory
        If IsNull(field) Then
            emptyFields = emptyFields & vbCr & field.Name
        End If
    Next field

'   |------------------- FILE NAMING AND SAVING -------------------|

    Dim tempFolder As String
    tempFolder = Environ("Temp")

    Dim outputFullPath As String
    outputFullPath = tempFolder & "\" & desired_filename & Format(Date, "ddmmyy") & Format(Time, "hhmmss") & ".docx"

    oDoc.SaveAs2 outputFullPath

    Set oWord = Nothing
    Set oDoc = Nothing

    If Len(emptyFields) > 0 Then
        MsgBox "Empty fields: " & vbCr & emptyFields
    End If

    templateReplacer = outputFullPath

errHandler:
    If Err.Number <> 0 Then
        MsgBox Err.Number & vbCr & Err.Description
    End If

    Set oWord = Nothing
    Set oDoc = Nothing

End Function

This is how I use it:
Code:
Sub Contract()
    Dim resultingFile As String
    resultingFile = templateReplacer("qryContract_Short", "client_id=264", "C:\Templates\CONTRACT.docx", "Contract")
    Application.FollowHyperlink resultingFile
End Sub

EDIT: this might be false.
If I recall correctly, I make two versions of query definitions, one with the full SQL code named "myQuery" and another one calling all the fields of that query using the * and then I call it "myQuery_Short". I do it like this to (sort of) avoid some of the issues of the 255 max characters limit for strings in VBA.

And it also requires this other function to work with fields longer than 255 characters.

Code:
#If VBA7 Then
  Private Declare PtrSafe Function GlobalUnlock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalLock Lib "kernel32" (ByVal hMem As LongPtr) As LongPtr
  Private Declare PtrSafe Function GlobalAlloc Lib "kernel32" (ByVal wFlags As LongPtr, _
    ByVal dwBytes As LongPtr) As LongPtr
  Private Declare PtrSafe Function CloseClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function OpenClipboard Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
  Private Declare PtrSafe Function EmptyClipboard Lib "user32" () As LongPtr
  Private Declare PtrSafe Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As LongPtr
  Private Declare PtrSafe Function SetClipboardData Lib "user32" (ByVal wFormat As LongPtr, _
    ByVal hMem As LongPtr) As LongPtr
#Else
  Private Declare Function GlobalUnlock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) As Long
  Private Declare Function GlobalAlloc Lib "kernel32" (ByVal wFlags As Long, _
    ByVal dwBytes As Long) As Long
  Private Declare Function CloseClipboard Lib "user32" () As Long
  Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As Long
  Private Declare Function EmptyClipboard Lib "user32" () As Long
  Private Declare Function lstrcpy Lib "kernel32" (ByVal lpString1 As Any, _
    ByVal lpString2 As Any) As Long
Private Declare Function SetClipboardData Lib "user32" (ByVal wFormat _
    As Long, ByVal hMem As Long) As Long
#End If

Const GHND = &H42
Const CF_TEXT = 1
Const MAXSIZE = 4096

Function ClipBoard_SetData(MyString As String)
'PURPOSE: API function to copy text to clipboard
'SOURCE: www.msdn.microsoft.com/en-us/library/office/ff192913.aspx

#If VBA7 Then
  Dim hGlobalMemory As LongPtr, lpGlobalMemory As LongPtr
  Dim hClipMemory As LongPtr, x As LongPtr
#Else
  Dim hGlobalMemory As Long, lpGlobalMemory As Long
  Dim hClipMemory As Long, x As Long
#End If

'Allocate moveable global memory
  hGlobalMemory = GlobalAlloc(GHND, Len(MyString) + 1)

'Lock the block to get a far pointer to this memory.
  lpGlobalMemory = GlobalLock(hGlobalMemory)

'Copy the string to this global memory.
  lpGlobalMemory = lstrcpy(lpGlobalMemory, MyString)

'Unlock the memory.
  If GlobalUnlock(hGlobalMemory) <> 0 Then
    MsgBox "Could not unlock memory location. Copy aborted."
    GoTo OutOfHere2
  End If

'Open the Clipboard to copy data to.
  If OpenClipboard(0&) = 0 Then
    MsgBox "Could not open the Clipboard. Copy aborted."
    Exit Function
  End If

'Clear the Clipboard.
  x = EmptyClipboard()

'Copy the data to the Clipboard.
  hClipMemory = SetClipboardData(CF_TEXT, hGlobalMemory)

OutOfHere2:
  If CloseClipboard() = 0 Then
    MsgBox "Could not close Clipboard."
  End If

End Function
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:22
Joined
Oct 29, 2018
Messages
21,358
I do it like this to (sort of) avoid some of the issues of the 255 max characters limit for strings in VBA.
Sorry, I am not aware of any 255 chars limit for Strings in VBA. Can you provide any link for a reference? Thanks.
 

561414

Active member
Local time
Today, 06:22
Joined
May 28, 2021
Messages
280
Sorry, I am not aware of any 255 chars limit for Strings in VBA. Can you provide any link for a reference? Thanks.
No, I can't provide any reference that is not anecdotical, unfortunately. I just recall the editor throwing errors about the SQL string being too long at some point.

I just tried with a query definition with a SQL statement longer than 700 characters and it worked, so I'm probably saying nonsense.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:22
Joined
Oct 29, 2018
Messages
21,358
No, I can't provide any reference that is not anecdotical, unfortunately. I just recall the editor throwing errors about the SQL string being too long at some point.

I just tried with a query definition with a SQL statement longer than 700 characters and it worked, so I'm probably saying nonsense.
Ok, thank you. I just wanted to make sure I wasn't missing something I wasn't aware of. Cheers!
 

Users who are viewing this thread

Top Bottom