Word Document into Report

mounty76

Registered User.
Local time
Yesterday, 20:11
Joined
Sep 14, 2017
Messages
345
Hi All,

I've got a 10 page contract that I currently use mail merge from Access to populate, it works fine (ish) when I'm using it, sometimes it opens up and doesn't recognize where the data source is (could be that the BE is on a server), but anyway when I'm using it its no problem as I just reconnect it.

Issue I have is that other users (not computer literate) are using the DB and struggling with getting the mail merge to work (including using the mailing section on word to filter through the results).

Ideally I'd like the contract to be a report which would make life easier for the end user.........I've read lots on this and it seems the only way is to have a load of subreports that each hold a page or so worth of data and then putting them together in one report.

Rather than typing out the entire 10 page contract and formatting it so it looks the same (its an approved contract so needs to be the same as the word document), is there another more efficient way to achieve the same sort or results?

Thanks in advance!
 
Further to this I should say that I've tried to copy and paste each page of the word document into a report then put a textbox linked to the data in the correct position, this works however the formatting doesn't resize to the correct page size.....other than writing the whole contract out I don't see how I can do this, when I copy and paste the words into a label then it comes up with an error the text is too long....
 
Are there special formatting in the contract that a rich text field in Access can't handle?
 
I don't think you will be happy with the results of a report so I wouldn't even go there.

I also don't like mail merge. That leaves us with OLE automation which is what I do. I'm going to post a sample so you can see how this works. The sample is straight blunt force but that's because it is an example. If you have only the one document, this method will be just fine. You only have to code it once and the code is actually pretty straight forward so you won't have trouble understanding it. However, if you have multiple documents with the potential for growth, then the best solution will be to use OLE but create tables in the database to define the mapping. So you have a table that lists the reports and the path to them unless that is stored in some other place. And another table that maps the table fields with the bookmarks in the documents.

Take a look at the sample and we can look at what to do from there.

 
While it might be tedious, if you have a lot of "boilerplate" and a little bit of unique data, it might be simpler to look at what Pat showed you and use the idea of having a couple of source files that you open and read line-by-line, essentially copying the boilerplate into ordinary paragraphs in a Word App Object that has the document open for you.

Here is a link where I sweated my way through creating a document in a Word App Object.

 
I don't think you will be happy with the results of a report so I wouldn't even go there.

I also don't like mail merge. That leaves us with OLE automation which is what I do. I'm going to post a sample so you can see how this works. The sample is straight blunt force but that's because it is an example. If you have only the one document, this method will be just fine. You only have to code it once and the code is actually pretty straight forward so you won't have trouble understanding it. However, if you have multiple documents with the potential for growth, then the best solution will be to use OLE but create tables in the database to define the mapping. So you have a table that lists the reports and the path to them unless that is stored in some other place. And another table that maps the table fields with the bookmarks in the documents.

Take a look at the sample and we can look at what to do from there.

Thanks Pat, looks ideal, I've only about 15 or so entries, the form wont change and so is straight forward to do......he says! lol. Thanks again, will have a try today and let you know how it goes
 
Issue I have is that other users (not computer literate) are using the DB and struggling with getting the mail merge to work (including using the mailing section on word to filter through the results).
you do not need to open it in Word to make a mailmerge.
if you created the MergeFields in word, you can automate it in Access.
 
I don't think you will be happy with the results of a report so I wouldn't even go there.

I also don't like mail merge. That leaves us with OLE automation which is what I do. I'm going to post a sample so you can see how this works. The sample is straight blunt force but that's because it is an example. If you have only the one document, this method will be just fine. You only have to code it once and the code is actually pretty straight forward so you won't have trouble understanding it. However, if you have multiple documents with the potential for growth, then the best solution will be to use OLE but create tables in the database to define the mapping. So you have a table that lists the reports and the path to them unless that is stored in some other place. And another table that maps the table fields with the bookmarks in the documents.

Take a look at the sample and we can look at what to do from there.

Getting there, I have an error code come up 5941- The requested member of the collection does not exist. I'm going to get told off for my field names having spaces in them, but I made this a long time ago and it have developed into something much bigger than I intended, to change them now isn't practical due to the time it would take, sorry!

I'm not sure if it is due to the field names or another issue? Here is my code that I have:

Private Sub cmdPrevLetter_Click()

On Error GoTo Err_cmdPrevLetter_Click

Call PopulateBookmarks

Exit_cmdPrevLetter_Click:
Exit Sub

Err_cmdPrevLetter_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_cmdPrevLetter_Click

End Sub

Public Sub PopulateBookmarks()
Dim sPathName As String
Dim sDocName As String
Dim sFamily As String

Dim iSeqNum As Integer

On Error GoTo Err_Proc:
Set db = CurrentDb()
'Fire up Word
Set WordApp = New Word.Application
WordApp.Visible = True ' make word visible
DoCmd.Hourglass True
sPathName = GetCurrentPathName
sDocName = QUOTE & sPathName & "Standard SEA.doc" & QUOTE
Set WordDoc = WordApp.Documents.Add(sDocName)


'Fill sFamily string
sFamily = ""
Set qdDAO = db.QueryDefs!SEA
qdDAO.Parameters![EnterID] = Me.ID
Set rsDAO = qdDAO.OpenRecordset
Do While rsDAO.EOF = False
sFamily = sFamily & rsDAO!FirstName & "; "
rsDAO.MoveNext
Loop
If Len(sFamily) > 1 Then
sFamily = Left(sFamily, Len(sFamily) - 2) ' remove trailing ;
End If
qdDAO.Close
rsDAO.Close
'Open recordset which will be used to populate bookmarks
Set qdDAO = db.QueryDefs!SEA
qdDAO.Parameters![EnterID] = Me.ID
Set rsDAO = qdDAO.OpenRecordset

WordDoc.FormFields("FullName").result = rsDAO!FirstName & " " & rsDAO!LastName
WordDoc.FormFields("FullName2").result = rsDAO!FirstName & " " & rsDAO!LastName
WordDoc.FormFields("Address").result = rsDAO![Home Address]
WordDoc.FormFields("DOB").result = rsDAO!DOB
WordDoc.FormFields("POB").result = rsDAO!POB
WordDoc.FormFields("YachtName").result = rsDAO![Yacht Name]
WordDoc.FormFields("YachtName1").result = rsDAO![Yacht Name]
WordDoc.FormFields("Position").result = rsDAO![Position for contract]
WordDoc.FormFields("Position1").result = rsDAO![Position for contract]
WordDoc.FormFields("Position2").result = rsDAO![Position for contract]
WordDoc.FormFields("Position3").result = rsDAO![Position for contract]
WordDoc.FormFields("Position4").result = rsDAO![Position for contract]
WordDoc.FormFields("Position5").result = rsDAO![Position for contract]
WordDoc.FormFields("StartDate").result = rsDAO![Start Date]
WordDoc.FormFields("Repat").result = rsDAO![Place of Repatriation]
WordDoc.FormFields("Wage").result = rsDAO![Monthly Salary]
WordDoc.FormFields("BenName").result = rsDAO![Account Name]
WordDoc.FormFields("BankName").result = rsDAO![Bank Name]
WordDoc.FormFields("BankAccount").result = rsDAO![Account Number]
WordDoc.FormFields("Routing").result = rsDAO![Routing Number]
WordDoc.FormFields("Swift").result = rsDAO![Sort or Swift Code]
WordDoc.FormFields("IBAN").result = rsDAO![IBAN Number]
WordDoc.FormFields("Leave").result = rsDAO![Leave Allowance]
WordDoc.FormFields("Place").result = rsDAO![Place Agreement Entered]
WordDoc.FormFields("DateEntered").result = rsDAO![Date Agreement Entered]






Set WordDoc = Nothing

MsgBox "Doc finished", vbOKOnly
Exit_Proc:
DoCmd.Hourglass False
Exit Sub
Err_Proc:
Select Case Err.Number
Case 4605
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Proc
End Select
Exit Sub



End Sub

Public Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)
Dim BMRange As Range

On Error GoTo PROC_ERR

Set BMRange = WordDoc.BookMarks(strBkmk).Range

BMRange.Text = varText & ""
WordDoc.BookMarks.Add strBkmk, BMRange
BMRange.Select

PROC_EXIT:
Exit Sub

PROC_ERR:
Select Case Err.Number
Case 4605 'this method or property is not available because the object is empty
Resume Next
Case 5941, 6028 ' member does not exist/the range cannot be deleted
MsgBox "Bookmark {" & strBkmk & "} There is a mapping error with this document. Please contact your administrator.", vbOKOnly
Resume Next
Case 91 'object variable not set
Resume Next
Case 4218 'type mismatch
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume PROC_EXIT
End Select
Resume PROC_EXIT

End Sub
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_cmdDelete_Click

End Sub



End Sub
 
you are using Bookmarks and FormFields, i thought you are using MergeField
 
I was initially using mergefields in word merge, but changed it to go with Pat's suggestion of using bookmarks. That said am interested to know how to do both (bookmark method and automate access for the word MergeField method)
 
here is a demo.
the Word contract is in attachment field in Contracts table.
extract it to see.
 

Attachments

1. When you post code use the feature that preserves formatting. Unformatted code becomes painful to read.
2. You have to tell us what line raised the error.
3. Don't apologize to me for the bad names. Apologize to yourself. Fixing the problem isn't as bad as it could be if you have an addin that finds all the instances and changes them regardless of where they exist. The Access find and replace works only inside a code module. It doesn't do properties/macros/ tables/queries. NameAutoCorrect only works with embedded tables (not linked tables) and although it does change queries/forms/reports, it still doesn't do properties or calculated fields.

I did a google search for this error since it is not coming from Access. Here's the most relevant link. Make sure your names all match. The sample I loaded includes a tool that might help to solve this problem. use the button to empty the table before running the search function to refill the table. Compare the names of the bookmarks to what you have in your code.

 
Pat's suggestion of using bookmarks.
wont work on multiple Records.
with bookmark you need to Load the data and print then load another set of data and print.
it will be effective only if you need to print one record at a time.
with mailmerge, everything is automatic.
 
wont work on multiple Records.
with bookmark you need to Load the data and print then load another set of data and print.
it will be effective only if you need to print one record at a time.
with mailmerge, everything is automatic.
The example you posted works great, just wondering how.......I have 3 different types of contract, would I make a table with the different contracts in that table.....then call the .docx from that table? I had been thinking of having the contract saved on the computer but it makes sense to have it saved in a table as an attachment.

It works great but struggling to understand the code!
 
wont work on multiple Records.
It's a sample arnel. It is intended to show how to use OLE not answer unasked questions. The source for the documents happens to be one record from a form. It also works exactly the way your sample works. ONE record at a time. The difference is that you used a continuous form and I used a single record form. That's because my sample additionally shows how to create tables and embed them in the document so you can include data from child tables.
Code:
Open recordset which will be used to populate bookmarks
    Set qdDAO = db.QueryDefs!qContacts
        qdDAO.Parameters![EnterContactID] = Me.ContactID
If you want the sample to work with a file of records, change the query to not select a specific Contact.
 
Last edited:
I'm not going to fight with arnel for your attention. But if you look at my example again, you will see that you can choose the document you want to populate. If you read my explanation again, you will see that I explained that since the sample bookmarks are hard-coded, you can create any document you want but it has to use the same bookmark names as I defined in my code.

I also said, that if you want something more flexible, you can't use the hard-coded bookmarks. They need to be stored in tables so you can use a loop to populate them. Then instead of potentially dozens of lines of code that takes one table field and populates a specific book mark, you need ONE line of code inside a loop that reads a table that contains the bookmark name and the field name so the code is essentially - Bookmarkvariablename = Fieldvariablename.
 
Hi Pat & Arnel,

Thanks both for your help and input, on this occasion I found that Arnel's solution worked easier (I really don't know which way is best but this I found easier and more suitable for my needs).

Thanks again......this forum and the people on it I have found consistently extremely helpful, doing a great job all of you!!
 
A nice to have would be if I can add some code to print to PDF the MailMerge document when it opens.....not the end of the world but if this is an easy thing to do your help would be much appreciated!
 
from within Word, you can Print to PDF.
 

Users who are viewing this thread

Back
Top Bottom