Merge with Word template and save with new filename (1 Viewer)

[Tim L]

How do you merge to a Word template and save with new filename?

1. Users click a command button.

2. A table is created (done via a make-table query) which contains all of the data from the currently displayed record which is to be linked to a Word Template file (unfortunately, due to some network issues, Word/the template file cannot access the query directly - at least that is the reason I have been told).

3. The word template file is opened via:

Dim objWord as Object
Set objWord = GetObject("filename","Word.Document")​

(this is not my knowledge, I got the idea from searching the forum)

4. The user views the template in Word, adjusting anything if necessary and printing off the document.

5. The user saves the document.

Problems:

1) The User still needs to click on the merge field toggle toolbar icon to get the correct data to display (if set to automatically display they need to click it twice, off then on).

2) How can I get the file to save with a predetermined filename? - One of the merge fields is already set up to provide this information and I have placed this field on the first line of the template as white text so that it remains hidden, but if opened using the above method the document is opened as the file and not as a new document based on the template - if opened manually then then Save is selected the filename is picked up (although typical Word it is ignoring everything after the first the underscore that I substituted for the spaces) .

I'm not that good with VBA, I you can't already tell! So a guiding hand on how to go about acheiving the above would be useful.

The key points are:

• The user needs to review the document before printing it.
• The whole process should be as transparent to the user as possible.
• I'm *really* stretching my VBA knowledge here!

I have seen some other examples (which looked very complicated) which involve passing the SQL for the mail-merge. I am dubious as to whether this will work due to the network access problem (only some of the queries can be seen by Word when the file is located where it will need to be stored). Also, the SQL for the query will be quite long; although I would only copy it out of the Query builder, it is still likely to cause an issue in the VBA editor, for readability, if for nothing else.

Tim

 

[Tim L]

Is it just impossible?

Okay, continued routing around and I still haven't found a solution. Additionally I am experiencing the same problems as described in the following threads:

http://www.access-programmers.co.uk/forums/showthread.php?t=121827

and

http://www.access-programmers.co.uk/forums/showthread.php?t=121827

Neither of which have solutions.

The main difference between my method and the ones show is that I just open the document, but is still isn't updating. Also, in the methods shown in the above threads an SQL statement is specified, is this entirely essential? For example, my Word document link is specified as a table with a single record, so no filtering/query is actually necessary.

Also, once the document has been opened and merged (if it can be achieved successfully) how do you get Word to save the document AND specify the filename to be used?

I'm beginning to think that what should be a simple process is going to be difficult to solve (and that the Network issue, which can't be changed, is going to be the sticking point), we really do not want to have to create our merge document in separate reports (there are at least four pages).

Assistance for this mere mortal from those of you with 'the knowledge' would be most welcome. (Grovel, grovel).

 

[adaniele]

merge from access to word

Tim,
I have a word template(.dot) which is populated using code. Then The new document is saved and the template is closed without saving it. The following code works for me in access 2000.

I hope this helps.

   Dim strDBName As String
   Dim strTable As String
   Dim strsql As String
   Dim strName As String
   Dim appWord As Object
   Dim strWordDoc As String
   Dim strDocsPath As String
   Dim strDocType As String
   Dim strSaveName As String
   Dim intSaveNameFail As String

'Create a Word instance
                                             Set appWord = CreateObject("Word.Application")
                                             appWord.Visible = False
                                             
                                             Dim dtMyDate, MyStr
                                             dtMyDate = Date
                                             MyStr = Format(dtMyDate, "ddmmyyyy")
                                             
                                             'Open the selected merge document
                                             strWordDoc = "<insert your template path>"

                                             appWord.Documents.Open strWordDoc
                                             strFileName = "<insert the new doc path>".doc"
                                             
                                             'Set the merge data source to the SQL statement, and do the merge
                                             strDBName = "<database path>"
                                             strsql = "select statement to populate your template;"
                                            
                                            With appWord
                                               .ActiveDocument.MailMerge.OpenDataSource name:=strDBName, _
                                                  LinkToSource:=True, SQLStatement:=strsql
                                               .ActiveDocument.MailMerge.Destination = wdSendToNewDocument
                                               .ActiveDocument.MailMerge.Execute
                                               .Documents(strWordDoc).Close savechanges:=wddonotsavechanges
                                               .ActiveDocument.SaveAs FileName:=strFileName, FileFormat:=wdFormatDocument
                                               .ActiveDocument.Close savechanges:=wddonotsavechanges
                                               .Quit
                                            End With
                                            'MsgBox "The document has been created and sent"

good luck,max

 

[Tim L]

Max,

thanks for your reply and my apologies for not replying sooner. Unfortunately I've moved jobs since I started the project that this thread was related to so I won't be able to test the code in the same environment. I will, hopefully get around to testing it on my home system, so thanks for posting the code.

Tim