Clean Up for Merge Code

[Ridge Kennedy]

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 M -WordMVP-]

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 Kennedy]

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 M -WordMVP-]

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
.Name property, usually) that the ActiveDocument is not, for some strange
reason, still the oDoc. But if you do oDoc.Close AFTER executing the merge, in
a new Word instance (as you have, here), then ActiveDocument should be the
only one left, anyway.

AND, if you're going to do further manipulation on ActiveDocument, assign it
to another object variable. This will be more efficient, as well as "safer".

Set oDocResult = ActiveDocument

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

 

[Doug Robbins]

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]

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 M -WordMVP-]

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