How to read the contents of a query into a mergefield.

[Abacus1234]

I have vba code which takes the content of a query, creates a .txt file and used mailmerge to create documents.

I would like to be able to read the contents of a query into a mergefield and use the mergefield in a document. Is anything like that possible?

or how could I get the rows of data in the query into a document? ( it would just be convenient to treat it as a particular mergefield.)

Set o_Doc = .Documents.Add(szWordPath & szDocumentFilename, False)
If CInt(o_App.Version) < 10 Then
'Word 97 and 2000
o_Doc.Mailmerge.OpenDataSource Name:=GetDBFilenamep(), _
ReadOnly:=False, Connection:=szConnection ', _
OpenExclusive:=False, SubType:=wdMergeSubTypeWord2000
Else
'Word xp and higher
'getting the query name without the string "QUERY" as the TDocuments table currently has it.


szQueryName = Replace(szConnection, "QUERY ", "")
'Currently storing the temporary text file at the same location as the database.
szTempTextFilePath = GetDBPath() & "/" & szQueryName & ".txt"
'Export the query result to a text file
DoCmd.TransferText acExportDelim, , szQueryName, szTempTextFilePath, True
'Perform mailmerge with the text file as the source
o_Doc.Mailmerge.OpenDataSource _
Name:=szTempTextFilePath, _
ReadOnly:=True
End If

[ItsMe]

Have you considered Automation of Word via VBA? Also, why bother using Word as a template at all? I would think a Report exported as PDF would be simplest, unless you need the .docx format. Even then, you should be able to export your report to doc or docx. It has been a while since I have tried it.

[Abacus1234]

Thanks for the response. I am using Automation of Word via VBA. Also I am using Access and Word to do mailmerge to documents. I am not looking to change this. I would like to add the functionality I described.

[ItsMe]

... I am using Automation of Word via VBA...

Then I would use DAO to store the results of your query in variables.

[Abacus1234]

Sorry, I cannot translate your reply. I'm currently doing many mailmerges using many different queries based on the code I am using. Each query produces 1 record and I can mailmerge any field. I run the same code using different queries to produce hundreds of documents. Now I want to add something different, I want to mailmerge a query with multiple rows(records, it would look like a table) into a document in addition to the mailmerge process I'm currently doing and I haven't a clue as to where to start.

[June7]

The Word merge would require code that populates a table in the Word document. I know I've worked with another poster on this. Unfortunately, probably never find the thread.

http://www.dslreports.com/forum/remark,20002102

See the two links in the answer for:
http://answers.microsoft.com/en-us/o...5-83139989eb38

[Abacus1234]

Thanks for the response. I will follow up on your suggestions.

[June7]

Bing: access word merge multitple records

Here is another http://www.msofficeforums.com/mail-m...e-records.html

[Abacus1234]

I maintain vba code but do not write from scratch. I want to explore using mailmerge to get the contents of a multi-record query.

I found this in another forum:
Sub Sequence_Table()
Dim WordApp As Object
Dim myDoc As Word.Document
Dim myTable As Word.Table
'
Const myDocName As String = "Y:\TestTable.doc"
'
Set WordApp = CreateObject("Word.Application")
'
With WordApp
Set myDoc = Documents.Add
myDoc.Tables.Add Range:=myDoc.Range(Start:=0, End:=0), NumRows:=5, NumColumns:=5
' .myDoc.Name = myDocName
myDoc.Close SaveChanges:=wdSaveChanges
End With
End Sub
When I try to add this to my code, Dim myTable As Word.Table causes an error and doesn't appear to be available in the Access library. (I need a solution that will work across all versions of Office).
These are a list of Dim Statements I have in my code.
'Variables forWord
Dim o_App AsObject
Dim szWordPath AsString
DimszDocumentFilename As String
DimszDocumentLabel As String
Dim szFocus AsString * 100
Dim szTempFileNameAs String
Dim o_Doc AsObject

[ItsMe]

Try adding the reference Microsoft Word XX.X Object Library. Where XX.X is represented by your version number of office, eg 14.0

[Abacus1234]

Thanks, that worked. The code uses early or late binding, I forget which, but generally I do not have to reference the Word Object Library. I wonder if when I have finished creating this piece of code, I will be able to remove the reference to Word. Thanks for your help.

[ItsMe]

You would do something like
Dim myTable As Object

for late binding

Then, when you go to instantiate the object, you may have to be explicit and include all parent objects. However, I do not see any early binding of the myTable object in the code posted here. It is already late binding as illustrated here.

With WordApp
Set myDoc = Documents.Add
myDoc.Tables.Add Range:=myDoc.Range(Start:=0, End:=0), NumRows:=5, NumColumns:=5
...

So you can probably delete
Dim myTable As Word.Table

[Abacus1234]

Thanks. I understand that now.

I have progressed further to the following code, but it gives me a 5922 error Word was unable to open the data source.I have the query with data in it, and I just want to see if I can get any of it to show up in the document. Sub Sequence_Table()
Dim o_App As Object
Dim o_Doc As Object
Dim myTable As Word.Table

Const szConnection As String = "QueryTGFEReport"
'
Const MyDocName As String = "c:\testtable.doc"
'
Set o_App = CreateObject("Word.Application")
'
With o_App
Set o_Doc = Documents.Add
o_Doc.Tables.Add Range:=o_Doc.Range(Start:=0, End:=0),NumRows:=25, NumColumns:=9
o_Doc.MailMerge.OpenDataSource Name:=GetDBFilenamep(), _
ReadOnly:=False, Connection:=szConnection, _
SubType:=8

o_Doc.MailMerge.Destination = 0
'o_Doc.MailMerge.Destination = wdSendToNewDocument
.Visible = True
.Activate
.WindowState = 1
'.WindowState = wdWindowStateMaximize
'.Activate (to bring up pop-up, move this to after Visible for WinVista, Win7 11.12.10)
o_Doc.MailMerge.Execute Pause:=False
o_Doc.CommandBars("MailMerge").Visible = False
'
o_Doc.Close SaveChanges:=wdSaveChanges
End With
End Sub

Function GetDBFilenamep()
'Return the full filename of the current database

Dim ThisWorkspace AsWorkspace
Dim ThisDatabase AsDatabase

Set ThisWorkspace =DBEngine.Workspaces(0)
Set ThisDatabase =ThisWorkspace.Databases(0)

'GetDBFilenamep =ThisDatabase.Name
GetDBFilenamep =currentDB.Name
End Function

[ItsMe]

When the error happens, if you click Debug, which line does it halt on? This one?
o_Doc.MailMerge.OpenDataSource Name:=GetDBFilenamep(), _
ReadOnly:=False, Connection:=szConnection, _
SubType:=8


I am not understanding this approach. If you are going to make a connection from word to Access I will guess you are going to need to define a recordset and a connection. I am not seeing that happening here. Never tried automating Mail Merge in Word. Let me see if I can find some info on it.

[ItsMe]

I was able to open an existing document that had nothing in it. It was just a word doc I created with a right click. After opening it, I was able to create a connection and be prompted to add data to the word doc. The prompt is controlled with this property (AddToRecentFiles:=True)

However, if you do not have Mail Merge fields defined in your word doc, no data will transfer. Using early binding, I will guess that you could add some fields via VBA. I found some members/methods to do this. It would probably be easier to create a template and use that. Actually, it would be easier to export an Access report and format as word but....

You can add a Mail Merge field by using the Mail Merge tool in the ribbon.

Here is the code to open the doc and create a connection. Once the connection is created, it is built in and does not need to be created a second time. You just need to add the Mail merge fields.

Dim myWordApp As Word.Application
Dim docNew As Word.Document
Set myWordApp = CreateObject("Word.Application")
myWordApp.Visible = True
Set docNew = myWordApp.Documents.Open("C:\Test\WordDocFolder\TestMailMerge.docx")

With docNew.MailMerge
    .MainDocumentType = wdFormLetters
    .OpenDataSource _
        Name:="C:\Test\AccessFolder\MailMerge_Sample.accdb", _
        LinkToSource:=True, AddToRecentFiles:=True, _
        Connection:="TABLE tblCustomers"
End With

docNew.Save
docNew.Close
myWordApp.Quit
Set docNew = Nothing
Set myWordApp = Nothing