Private Sub Document_Open() ActiveDocument.MailMerge.MainDocumentType = wdFormLetters ActiveDocument.MailMerge.OpenDataSource Name:= _ "C:\mydir\mydata.xls" _ , ConfirmConversions:=False, ReadOnly:=True, LinkToSource:=True, _ AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _ WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _ Format:=wdOpenFormatAuto, Connection:= _ "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\mydir\mydata.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet O" _ , SQLStatement:="SELECT * FROM `PrintOut`", SQLStatement1:="", _ OpenExclusive:=False, SubType:=wdMergeSubTypeAccess End SubI’ve tried adding the following code to the word document close with no effect.
Private Sub Document_Close() ActiveDocument.MailMerge.DataSource.Close End SubHere is the excel code that launches word and automates it. I’m guessing that this is ok because if I have it run using a non-mail merge doc I don’t see the problem. But at this point I’m really not sure.
Sub StepOne() strpath = "C:\mydir\" ' 'Start Word Dim ox As Object Set ox = CreateObject("word.application") ox.Visible = True ox.Options.DefaultOpenFormat = 1 'Open MyMailmerge.com ox.Documents.Open Filename:=strpath + "mymailmerge.doc" 'ox.Documents.Open Filename:=strpath + "mynonmailmerge.doc" ox.Selection.WholeStory ox.Selection.Copy ox.activedocument.Close 'Open A new blank doc Set docNew = ox.Documents.Add tempstr = strpath + "mydata.xls" docNew.mailmerge.MainDocumentType = 0 'Set Data source in new doc docNew.MailMerge.OpenDataSource Name:="" + tempstr + "", _ ReadOnly:=True, _ Connection:= _ "Provider=MSDASQL.1;Persist Security Info=True;Extended Properties=""DSN=Excel Files;DBQ=C:\mydir\mydata.xls;DefaultDir=C:\mydir;DriverId=790;MaxBuf" _ , SQLStatement:="SELECT * FROM `PrintOut`", SQLStatement1:="" ' 'Paste Check set to new doc Set Range2 = docNew.Content Range2.Collapse Direction:=0 Range2.Paste With ox.activedocument.MailMerge .Destination = 1 .SuppressBlankLines = True With .DataSource .FirstRecord = 1 .LastRecord = -16 End With .Execute Pause:=False End With ox.activedocument.Close SaveChanges:=0 Set docNew = Nothing Set Range2 = Nothing ox.Quit Set ox = Nothing End Sub