Clean Up for Merge Code

Dear All,

I have a template that is creating a new document using data from an access
query that is merged into a Word template. All Office 2003 apps on Win2K
and XP pro workstations.

The document is created -- but there is also an additional "Document 1" that
is created every time that has to be deleted -- a copy of the template with
no data in it. If anyone can point out how I can eliminate that extra
clutter, I would appreciate it.

Additionally, my code is messy, since a lot of it was generated by
recording. I'd appreciate gentle recommendations for cleaning it up.

Right now, it says"

Sub autonew()
' Perform Merge using CatalogTextSource.txt
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\TEMP\MtgNoticeSource.txt", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto

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

' Save New Merge Document as NewMeetingNotice.doc
ChangeFileOpenDirectory "C:\My Documents\"
ActiveDocument.SaveAs FileName:="\\cpa_comp\chapters\_chapter
Programs\-Chapter Notice E-Form\__Current\NewMeetingNotice.doc",
FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False

Followed by code to clean up and format the document

Thnak you in advance for any assistance.

Sincerely,

Ridge (in New Joisey)

Cindy
Fri Aug 19 04:31:51 CDT 2005

Hi Ridge,

> I have a template that is creating a new document using data from an access
> query that is merged into a Word template. All Office 2003 apps on Win2K
> and XP pro workstations.
>
> The document is created -- but there is also an additional "Document 1" that
> is created every time that has to be deleted -- a copy of the template with
> no data in it. If anyone can point out how I can eliminate that extra
> clutter, I would appreciate it.
>
Are you absolutely certain that "Document 1" is generated from your template?
Or is it the default document Word creates when Word is started? There's
nothing *in the code you show us* that creates this document. But OTOH, you
don't show us how you're connecting to the Word application...

> Additionally, my code is messy, since a lot of it was generated by
> recording. I'd appreciate gentle recommendations for cleaning it up.
>
I'd say the only thing in there you could get rid of is the
ChangeFileOpenDirectory line. Some of the arguments in the various methods
might not be strictly required, but leaving them in isn't going to slow the
macro down, or anything.

>
> Sub autonew()
> ' Perform Merge using CatalogTextSource.txt
> ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
> ActiveDocument.MailMerge.OpenDataSource Name:= _
> "C:\TEMP\MtgNoticeSource.txt", ConfirmConversions:=False, _
> ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
> PasswordDocument:="", PasswordTemplate:="",
> WritePasswordDocument:="", _
> WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto
>
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .MailAsAttachment = False
> .MailAddressFieldName = ""
> .MailSubject = ""
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = wdDefaultFirstRecord
> .LastRecord = wdDefaultLastRecord
> End With
> .Execute Pause:=True
> End With
>
> ' Save New Merge Document as NewMeetingNotice.doc
> ChangeFileOpenDirectory "C:\My Documents\"
> ActiveDocument.SaveAs FileName:="\\cpa_comp\chapters\_chapter
> Programs\-Chapter Notice E-Form\__Current\NewMeetingNotice.doc",
> FileFormat:= _
> wdFormatDocument, LockComments:=False, Password:="",
> AddToRecentFiles:= _
> True, WritePassword:="", ReadOnlyRecommended:=False,
> EmbedTrueTypeFonts:= _
> False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
> SaveAsAOCELetter:=False
>

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

Ridge
Fri Aug 19 13:51:13 CDT 2005

Dear Cindy,

You said:

> Are you absolutely certain that "Document 1" is generated from your
template?

The Document1 (or sometimes Document2, or Document3 . . . ) is a copy of the
template with all the mergefield codes in it.

> Or is it the default document Word creates when Word is started? There's
> nothing *in the code you show us* that creates this document. But OTOH,
you
> don't show us how you're connecting to the Word application...

Ahh. Good point. Here is the code associated with the command button in an
Access form in our reporting database:

Private Sub cmdSeminarExp_Click()
On Error GoTo cmdSeminarExp_Click_Err

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryNJEvnt_CatalogDetailSeminarsWrdMrg"
DoCmd.OpenQuery "qryNJEvnt_CatalogDetailExportWrdMrg"

DoCmd.TransferText acExportDelim, , "tblNJEvnt_CatalogWrdMrgDataExport",
"c:\my documents\CatalogTextSource.txt", True


Dim LWordDoc As String
Dim oApp As Object

'Create an instance of MS Word
Set oApp = CreateObject(Class:="Word.Application")
oApp.Visible = True

'Open the Document
oApp.Documents.Add
template:="\\Cpa_comp\Workgroups\AutomationTemplates\NewCatalogTextWrdMrg.do
t", NewTemplate:=False, DocumentType:=0

DoCmd.Close

> I'd say the only thing in there you could get rid of is the
> ChangeFileOpenDirectory line. Some of the arguments in the various methods
> might not be strictly required, but leaving them in isn't going to slow
the
> macro down, or anything.

OK.

Thnak you for your interest.

Sincerely,

R.

Cindy
Sun Aug 21 02:44:07 CDT 2005

Hi Ridge,

> The Document1 (or sometimes Document2, or Document3 . . . ) is a copy of the
> template with all the mergefield codes in it.
>
Ah, I think I'm with you, now. First, some comments about the code insert

> Set oApp = CreateObject(Class:="Word.Application")
> oApp.Visible = True
'''You might want to put a oApp.Activate in here, somewhere, if it's not later
in the code

> 'Open the Document
'''Set an object variable to handle this document
Dim oDoc as Word.Document
Set odDoc = oApp.Documents.Add( _
template:="\\Cpa_comp\Workgroups\AutomationTemplates\NewCatalogTextWrdMrg.do
t", NewTemplate:=False, DocumentType:=0)

'''Once you're finished EXECUTING the mail merge
oDoc.Close SaveChanges:=wdDoNotSaveChanges

This oDoc is the Document1 (or whatever). What you're seeing is normal for
Word when you create a new document from the template. Executing mail merge
generates an additional document.

Use oDoc *INSTEAD of ActiveDocument* in the code you showed me before, up to
the point where you do SaveAs. The ActiveDocument at this point is the mail
merge result. It's always a good idea to include a check (of the document

Doug
Sun Aug 21 04:16:40 CDT 2005

See the single document mailmerge solution that was created by fellow MVP,
Albert Kallal at

http://www.members.shaw.ca/AlbertKallal/wordmerge/index.html


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"Ridge Kennedy" <rkennedy@njscpa.org> wrote in message
news:OH1Zbd1oFHA.2472@TK2MSFTNGP15.phx.gbl...
> Dear All,
>
> I have a template that is creating a new document using data from an
> access
> query that is merged into a Word template. All Office 2003 apps on Win2K
> and XP pro workstations.
>
> The document is created -- but there is also an additional "Document 1"
> that
> is created every time that has to be deleted -- a copy of the template
> with
> no data in it. If anyone can point out how I can eliminate that extra
> clutter, I would appreciate it.
>
> Additionally, my code is messy, since a lot of it was generated by
> recording. I'd appreciate gentle recommendations for cleaning it up.
>
> Right now, it says"
>
> Sub autonew()
> ' Perform Merge using CatalogTextSource.txt
> ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
> ActiveDocument.MailMerge.OpenDataSource Name:= _
> "C:\TEMP\MtgNoticeSource.txt", ConfirmConversions:=False, _
> ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
> PasswordDocument:="", PasswordTemplate:="",
> WritePasswordDocument:="", _
> WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto
>
> With ActiveDocument.MailMerge
> .Destination = wdSendToNewDocument
> .MailAsAttachment = False
> .MailAddressFieldName = ""
> .MailSubject = ""
> .SuppressBlankLines = True
> With .DataSource
> .FirstRecord = wdDefaultFirstRecord
> .LastRecord = wdDefaultLastRecord
> End With
> .Execute Pause:=True
> End With
>
> ' Save New Merge Document as NewMeetingNotice.doc
> ChangeFileOpenDirectory "C:\My Documents\"
> ActiveDocument.SaveAs FileName:="\\cpa_comp\chapters\_chapter
> Programs\-Chapter Notice E-Form\__Current\NewMeetingNotice.doc",
> FileFormat:= _
> wdFormatDocument, LockComments:=False, Password:="",
> AddToRecentFiles:= _
> True, WritePassword:="", ReadOnlyRecommended:=False,
> EmbedTrueTypeFonts:= _
> False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
> SaveAsAOCELetter:=False
>
> Followed by code to clean up and format the document
>
> Thnak you in advance for any assistance.
>
> Sincerely,
>
> Ridge (in New Joisey)
>
>

Ridge
Tue Aug 23 11:04:24 CDT 2005

Dear Cindy,

Great help again. Thank you.

A follow-up question.

If I set an oDoc variable in Access, does it carry over into Word when the
code in Access ends and the Word autonew Macro takes over? Same sort of
question about the Word application object: if I say in the access code
after proper declarations:

oApp.visible = false

can I then later, after autonew does its thing, say

oApp.visible = true

Or do I have to declare a new variable as part of the Word code.

Sincerely,

Ridge

Cindy
Wed Aug 24 03:06:57 CDT 2005

Hi Ridge,

> A follow-up question.
>
I'm not quite sure I follow this, but let me take a shot:

If Access creates a Word document from to a template with an AutoNew macro
that macro will execute. Under normal circumstances, this shouldn't affect
the oDoc object variable (as long as the AutoNew doesn't close the document
and re-open it, for example).

The same goes for oApp. Where you might run into problems is determining
when AutoNew has finished executing...

> If I set an oDoc variable in Access, does it carry over into Word when the
> code in Access ends and the Word autonew Macro takes over? Same sort of
> question about the Word application object: if I say in the access code
> after proper declarations:
>
> oApp.visible = false
>
> can I then later, after autonew does its thing, say
>
> oApp.visible = true
>
> Or do I have to declare a new variable as part of the Word code.
>

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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