(Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes 
Author Message
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes

I am working on a automation merge from Access to Word.  The code works fine
but it leaves open both the document with the merge codes and the final
merge document.  Can anyone reccomend code to automatically close the
document containing the merge codes, while leaving the final merged document
open?

Thanks, Michael

****************************************************************************
*****
                         ******* Here is the code so far. ****
****************************************************************************
*****

Private Sub ClientLetterUsingTempFile()

    Dim objWord As Word.Application

    DoCmd.Hourglass True

    On Error Resume Next

    ' Attempt to reference Word which is already running.
    Set objWord = GetObject(, "Word.Application")

    ' If true, Word is not running.
    If objWord Is Nothing Then
        ' Create a new instance of the Word application.
        Set objWord = New Word.Application
        ' If true, MS Word 9.0 is not installed.
        If objWord Is Nothing Then
            MsgBox "MS Word 9.0 is not installed on your computer"
        End If
    End If

    ' On Error GoTo ErrorHandler (Put Error Handler Code Here!)

    ' Write the Client data to a temporary temp file to use with _
    ' the mail merge.
    DoCmd.OutputTo acOutputQuery, "qryClientLetters", _
        acFormatRTF, "C:\Windows\Merge\Temp.RTF", False

    objWord.Documents.Add ("C:\Windows\Merge\Client Letter.dot")

    ' Run the Macro in the Word Document that does the Mail Merge
    objWord.Run "RunMailMerge"

    DoCmd.Hourglass False

    ' Activates Word
    objWord.Activate

    ' Show Word to the user.
    objWord.Visible = True

    ' Release the object variable.
    Set objWord = Nothing

End Sub

*******************************************************
******  And here is the macro within Word  ******
*******************************************************

Sub RunMailMerge()

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
     End With

End Sub



Sat, 24 Jan 2004 12:43:30 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Michael,

Quote:
> The code works fine
> but it leaves open both the document with the merge codes and the final
> merge document.  Can anyone reccomend code to automatically close the
> document containing the merge codes, while leaving the final merged document
> open?

Make a couple of changes here:
    Dim docMainMerge as Word.Document
    Set docMainMerge = _
        objWord.Documents.Add ("C:\Windows\Merge\Client Letter.dot")
    'Run the mail merge
    docMainMerge.Close
    'Activate Word and make it visible
    Set objWord = Nothing

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)



Sat, 24 Jan 2004 20:40:40 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Thanks Cindy,

Works great!

I added the DoNotSaveChanges variant to the close method to eliminate the
Save As prompt.

 docMainMerge.Close SaveChanges:=wdDoNotSaveChanges

Michael



Sun, 25 Jan 2004 11:27:47 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
I noticed a new minor problem in my merge code.

If I merge one client letter and then move onto another client and try to
merge a second letter, the second letter still contains the data for the
first client.  (This does not happen if I close the first letter before
merging the second letter).

I assume this has something to do with the merge letter remaining linked to
the initial data.

Would appreciate help in finding a method to work around this problem so
that I can merge a second client's information while leaving the first
client's letter open.

Michael



Sun, 25 Jan 2004 14:19:43 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Michael,

Quote:
> If I merge one client letter and then move onto another client and try to
> merge a second letter, the second letter still contains the data for the
> first client.  (This does not happen if I close the first letter before
> merging the second letter).

I think you'd better show us the code (copy/paste into your reply) used to
perform the "merge". I have a suspicion or two, but I don't want to send
you haring down the wrong track if you're doing things another way :-)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)



Sun, 25 Jan 2004 18:16:21 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Thanks for the help Cindy.  I am just starting to learn automation and
probably
need to spend a lot more time reading some of the articles.  Here are my
questions:

The following code:

    (1)   Sends the results of a query to an .rtf file.

    (2)   Runs a macro in a Word merge template to merge the data in the
.rtf file.

    (3)   Saves the resulting merge document under a name passed by a
variable
            that includes the name of the client and the name of the
template.

Here are the challenges that I have encountered.

    (1)    If the user leaves the merged document open and then tries to
merge
            the template with a different client, the second merged letter
contains the data from the first client.

    (2)    Ideally, I would like users on different work stations to use the
templates from a single directly.
            The problem is that each user will need a separate .rtf file to
allow for different data to be used
            simultaneously by each user.  I am not sure how this would be
possible since each merge
            template links to a single .rtf file.

    (3)    When I use the SaveAs method in Word, it automatically overwrites
existing documents
            with the same name.  I would like to have the code save the
document under a new name
            if the document name already exists in the directory.  Ie.
"DocumentName1.doc " instead of
            replacing "DocumentName.doc"

Sorry about the length of the message, Michael

****************************************************************************
**************
*
*
*            The following code is inside the Access "Case Edit New Form"
*
*
*
****************************************************************************
**************

Private Sub ClientLetterUsingTempFile()

    'The strTemplate is the merge template that was previously selected
through an API call.

           Dim strTemplate As String
           strTemplate = Forms![Case Edit New Form]![txtFileOpen]

            Dim objWord As Word.Application

            DoCmd.Hourglass True

            On Error Resume Next

    ' Attempt to reference Word which is already running.
    Set objWord = GetObject(, "Word.Application")
    ' If true, Word is not running.

        If objWord Is Nothing Then
            ' Create a new instance of the Word application.
            Set objWord = New Word.Application
            ' If true, MS Word 9.0 is not installed.
            If objWord Is Nothing Then
                MsgBox "MS Word 9.0 is not installed on your computer"
            End If
        End If

    ' On Error GoTo ErrorHandler (Put Error Handler Code Here!)

    ' Write the Client data to a temporary temp file to use with _
    ' the mail merge.

        DoCmd.OutputTo acOutputQuery, "qryClientLetters", _
        acFormatRTF, "C:\Windows\Merge\Temp.RTF", False

        Dim docMainMerge As Word.Document
                Set docMainMerge = _
                objWord.Documents.Add(strTemplate)

    ' Run the Macro in the Word Document that does the Mail Merge
            objWord.Run "RunMailMerge"

        DoCmd.Hourglass False

        docMainMerge.Close SaveChanges:=wdDoNotSaveChanges

    ' Activates Word
        objWord.Activate

    ' Show Word to the user.
        objWord.Visible = True

    ' Release the object variable.
        Set objWord = Nothing

End Sub

****************************************************************************
**************
*
*
*                                    And here is the macro within Word
                             *
*                                that runs the merge and saves the document
*
*
*
****************************************************************************
**************

Sub RunMailMerge()

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
     End With

ActiveDocument.SaveAs FileName:=varSaveName

End Sub



Mon, 26 Jan 2004 14:56:17 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Michael,

Quote:
> The following code:

Does not include the code that actually performs the mail
merge. This is the code I need to see:

 ' Run the Macro in the Word Document that does the Mail
Merge
            objWord.Run "RunMailMerge"

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail
:-)



Mon, 26 Jan 2004 17:36:45 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi, Cindy,

I hope I understand.  This code was at the very end of my last message.
It is a macro in the word document that executes the merge and then saves
the document.

Sub RunMailMerge()

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .Execute
     End With

ActiveDocument.SaveAs FileName:=varSaveName

End Sub



Quote:
> Hi Michael,

> > The following code:

> Does not include the code that actually performs the mail
> merge. This is the code I need to see:

>  ' Run the Macro in the Word Document that does the Mail
> Merge
>             objWord.Run "RunMailMerge"

> Cindy Meister
> INTER-Solutions, Switzerland
> http://homepage.swissonline.ch/cindymeister
> http://www.mvps.org/word
> http://go.compuserve.com/MSOfficeForum

> This reply is posted in the Newsgroup; please post any
> follow question or reply in the newsgroup and not by e-mail
> :-)



Mon, 26 Jan 2004 20:40:50 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Michael,

Quote:
> This code was at the very end of my last message.

Missed that.

Quote:
> > If I merge one client letter and then move onto another client and try to
> > merge a second letter, the second letter still contains the data for the
> > first client.  (This does not happen if I close the first letter before
> > merging the second letter).
> ' Write the Client data to a temporary temp file to use with _
>     ' the mail merge.

>         DoCmd.OutputTo acOutputQuery, "qryClientLetters", _
>         acFormatRTF, "C:\Windows\Merge\Temp.RTF", False

The problem is here, I think. The data source is not a query in your Access
DB, but a separate file in delimited format, I take it. OK. Word saves the
path to the data source hard-coded in the document's binary file format. And
an open main merge document also opens (and therefore locks) the data source.
I'd say the data source isn't released when this runs again, so you're not
able to even CREATE Temp.RTF - it can't overwrite the previous version if
that is locked because it's open.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)



Mon, 26 Jan 2004 21:29:12 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Cindy, I think you're right.  I appreciate your insight and patience.  The
rtf file seemed to work well when I first set up the merge but I recognize a
few of the limitations.  I am going to spend a couple of days working on a
revision of the data link.

Thanks,
Michael



Quote:
> Hi Michael,

> > This code was at the very end of my last message.

> Missed that.

> > > If I merge one client letter and then move onto another client and try
to
> > > merge a second letter, the second letter still contains the data for
the
> > > first client.  (This does not happen if I close the first letter
before
> > > merging the second letter).
> > ' Write the Client data to a temporary temp file to use with _
> >     ' the mail merge.

> >         DoCmd.OutputTo acOutputQuery, "qryClientLetters", _
> >         acFormatRTF, "C:\Windows\Merge\Temp.RTF", False

> The problem is here, I think. The data source is not a query in your
Access
> DB, but a separate file in delimited format, I take it. OK. Word saves the
> path to the data source hard-coded in the document's binary file format.
And
> an open main merge document also opens (and therefore locks) the data
source.
> I'd say the data source isn't released when this runs again, so you're not
> able to even CREATE Temp.RTF - it can't overwrite the previous version if
> that is locked because it's open.

> Cindy Meister
> INTER-Solutions, Switzerland
> http://homepage.swissonline.ch/cindymeister
> http://www.mvps.org/word
> http://go.compuserve.com/MSOfficeForum

> This reply is posted in the Newsgroup; please post any follow question or
> reply in the newsgroup and not by e-mail :-)



Wed, 28 Jan 2004 10:18:38 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Michael,

Basically, RTF is a pretty good file format for a Word merge. Lots of people
use and recommend it. But given the "multiple access" nature you describe,
what I might do is to save the Word main merge doc/template *without* the data
source attached. Then attach it as part of the VBA automation, right after you
open the Word document. This will allow you the necessary flexibility for the
file name, so you could create the RTF files with incrementing names, for
example.

Quote:
> The
> rtf file seemed to work well when I first set up the merge but I recognize a
> few of the limitations.  I am going to spend a couple of days working on a
> revision of the data link.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)



Fri, 30 Jan 2004 20:58:29 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Thanks Cindy,

You have been a great help.  The merge is finally working correctly.  I
really like the idea of continuing to use the RTF file and waiting to
perform the data link at the time of the automation merge.  Seems to work
very efficiently.

I have one final issue of the project.  I hope that you can help.  At the
end of the merge I have inserted code to save the final document under a
name that is a composite of the client name and the document name.  It works
fine the first time that I run the merge.  However, if I run the merge a
second time, the code saves the first document under the new name and leaves
the second merged document unsaved.

If I close the documents, then the merge part still works but then new
document is not saved.

If I close the database and open it again, the code once again works
properly in saving and naming the first merged document.

Here is the code

'****************
'****************
'****************

'MICROSOFT ACCESS - Merge Module
'1. Opens Word
'2.Runs RunMailMerge Macro in Word which links to data source and them
performs merge
'3 Attempts to save final document as "CLIENT NAME (Document Name)

Option Compare Database
    Public varTxtFileOpen As String
    Public varDocumentName As String

Public Sub WordClientLetterUsingTempFile()

   Dim strTemplate As String
   strTemplate = Forms![Case Edit New Form]![txtFileOpen]

    varDocumentName = Forms![Case Edit New Form]![txtLastFirst] _
        & " (" & Forms![Case Edit New Form]![txtFileTitle] & ").doc"

    ' Use GetObject to activate an application that is already running. _
    ' If the application is not running, start the application with the _
    ' "New" keyword for early binding.

    Dim objWord As Word.Application

    DoCmd.Hourglass True

    ' Resume to the next line following the error.
    On Error Resume Next

    ' Attempt to reference Word which is already running.
    Set objWord = GetObject(, "Word.Application")

    ' If true, Word is not running.
    If objWord Is Nothing Then
        ' Create a new instance of the Word application.
        Set objWord = New Word.Application
        ' If true, MS Word 8.0 is not installed.
        If objWord Is Nothing Then
            MsgBox "MS Word 8.0 is not installed on your computer"
        End If
    End If

    ' On Error GoTo ErrorHandler (Put Error Handler Code Here!)

    ' Write the Employee data to a temporary temp file to use with _
    ' the mail merge.
    DoCmd.OutputTo acOutputQuery, "qryEmployeeLetters", _
        acFormatRTF, "C:\DataDirectory\Temp.RTF", False

    Dim docMainMerge As Word.Document
    Set docMainMerge = _
        objWord.Documents.Add(strTemplate)

'*******
 ' Run the Macro in the Word Document that does the Mail Merge

    objWord.Run "RunMailMerge"

    DoCmd.Hourglass False

 docMainMerge.Close SaveChanges:=wdDoNotSaveChanges

    ' Activates Word
    objWord.Activate

'*******
'SAVES THE DOCUMENT as varDocumentName - String including client name and
template

        ChangeFileOpenDirectory "C:\WINDOWS\Desktop\"
    ActiveDocument.SaveAs FileName:=varDocumentName, FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False

    ' Show Word to the user.
    objWord.Visible = True

    ' Release the object variable.
    Set objWord = Nothing

End Sub

'****************
'****************
'****************

'WORD MERGE MACRO - RunMailMerge
' Template Links Document to Data Source and Performs Merge

Sub RunMailMerge()

    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\DataDirectory\Temp.RTF", ConfirmConversions:=False _
        , ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
        Connection:="", SQLStatement:="", SQLStatement1:=""

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=True
    End With

End Sub



Sun, 01 Feb 2004 13:09:26 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Michael,

Quote:
> At the
> end of the merge I have inserted code to save the final document under a
> name that is a composite of the client name and the document name.  It works
> fine the first time that I run the merge.  However, if I run the merge a
> second time, the code saves the first document under the new name and leaves
> the second merged document unsaved.

I suspect this has to do with how you're declaring, setting and releasing
object variables and instances of Word, but it would really hope if you could
(again) copy the relevant part of the code in that performs the merge and
names the results.

One tip I can give you: watch out for the use of ActiveDocument. If I had to
bet on where the problem is, this would be it. Declare an object variable and
set it to the main merge doc as you open it. And set another object variable
to the mail merge document as soon as execution has finished and use this for
SaveAs, .Close, etc. (Unfortunately, the Word Dev team didn't realize there
could be confusion about what document is the merge result, so there is no way
to get this document directly before Word 2002).

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)



Sun, 01 Feb 2004 19:38:11 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Cindy,

I think you're right.  The problem must have to do with using the
ActiveDocument object.  Below are the pertinent parts of the code.  I will
try to modify the code tonight.

'*** Here is the part of the code that runs the merge. (Macro in Word).

Sub RunMailMerge()

    ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\DataDirectory\Temp.RTF", ConfirmConversions:=False _
        , ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
        Connection:="", SQLStatement:="", SQLStatement1:=""

    With ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .MailAsAttachment = False
        .MailAddressFieldName = ""
        .MailSubject = ""
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=True
    End With

End Sub

'*** And here is the part that saves the result.  (Access Module).

        ChangeFileOpenDirectory "C:\WINDOWS\Desktop\"
    ActiveDocument.SaveAs FileName:=varDocumentName, FileFormat:= _
        wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
        True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
        False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
        SaveAsAOCELetter:=False



Sun, 01 Feb 2004 23:09:00 GMT  
 (Access/Word Merge) Automating Closing of Main Doc Containing Merge Codes
Hi Cindy,

I think I finally got the right code to save the merged document with
automation.  I used the ordinal object position of the last document instead
of the ActiveDocument constant.

The old command was:

    ActiveDocument.SaveAs FileName:=varDocumentName

And the new command is:

    Word.Documents(1).SaveAs FileName:=varDocumentName

Also at the end of the procedure, I shift focus to the new document:

    Word.Documents(1).Activate

I haven't tested this very much yet, so I may be premature. But, so far, so
good.

Thank you very much for the help.  I was really lost.

Michael



Mon, 02 Feb 2004 15:45:15 GMT  
 
 [ 16 post ]  Go to page: [1] [2]

 Relevant Pages 

1. (Access/Word Merge) Automating Saving of Merge Results

2. Automated Mail merge from access to word

3. automate merging data between word and access 2000

4. Automating Word Mail merge from a password-protected Access database

5. Word Mail Merge Err - Word could not merge...

6. Formatting Merge Fields in Word Mail Merge

7. Merging data from vb form into Word 2000 merge

8. Transfering Text from Access to Word for Mail Merge through Code

9. NEED HELP, Coding Access to Word Mail Merge :-)

10. mail merge coding with MS access in word

11. Transfer Text from Access to Word for Mail Merge by Code

12. Automating a Word mail merge

 

 
Powered by phpBB® Forum Software