 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / August 2005Clean 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) 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 [quoted text clipped - 5 lines] > 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() [quoted text clipped - 31 lines] > 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 :-) 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. 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 :-) 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 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 [quoted text clipped - 8 lines] > > 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 :-) See the single document mailmerge solution that was created by fellow MVP, Albert Kallal at http://www.members.shaw.ca/AlbertKallal/wordmerge/index.html  SignatureHope 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 > Dear All, > [quoted text clipped - 57 lines] > > Ridge (in New Joisey)
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.