Problem with MailMerge.OpenDataSource

[snecz]

Hi,<o></o>
<o> </o>
I take data from a spreadsheet, open MS Word and create letters using mail merge. Everything is automated in my excel vba code. Everything is fine until code “With docWD.MailMerge<o></o>
.OpenDataSource Name:= _ …”. Then I can see “Run-time error ‘4198’.<o></o>
What is wrong with my MailMerge.OpenDataSource method?<o></o>
Thanks a lot for your help.

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CMISKIE%7E1%5CUSTAWI%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:HyphenationZone>21</w:HyphenationZone>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><style> <!--  /* Style Definitions */  p.MsoNormal, li.MsoNormal, div.MsoNormal     {mso-style-parent:"";     margin:0cm;     margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:12.0pt;     font-family:"Times New Roman";     mso-fareast-font-family:"Times New Roman";} @page Section1     {size:595.3pt 841.9pt;     margin:70.85pt 70.85pt 70.85pt 70.85pt;     mso-header-margin:35.4pt;     mso-footer-margin:35.4pt;     mso-paper-source:0;} div.Section1     {page:Section1;} --> </style><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable     {mso-style-name:Standardowy;     mso-tstyle-rowband-size:0;     mso-tstyle-colband-size:0;     mso-style-noshow:yes;     mso-style-parent:"";     mso-padding-alt:0cm 5.4pt 0cm 5.4pt;     mso-para-margin:0cm;     mso-para-margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Times New Roman";     mso-ansi-language:#0400;     mso-fareast-language:#0400;     mso-bidi-language:#0400;} </style> <![endif]-->  Dim appWD As Object<o:p></o:p>
  Dim docWD As Object<o:p></o:p>
  Set appWD = CreateObject("Word.Application")<o:p></o:p>
  appWD.Visible = True<o:p></o:p>
  Set docWD = appWD.documents.Open(file.xls)<o:p></o:p>
  <o:p> </o:p>
  With docWD.MailMerge<o:p></o:p>
  .OpenDataSource Name:= _<o:p></o:p>
          "H:\Source\file.xls", ConfirmConversions:=False, ReadOnly:= _<o:p></o:p>
          False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _<o:p></o:p>
           PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _<o:p></o:p>
          "", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _<o:p></o:p>
          "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source= H:\Source\file.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Typ" _<o:p></o:p>
          , SQLStatement:="SELECT * FROM `data$'`", SQLStatement1:="", _<o:p></o:p>
          SubType:=wdMergeSubTypeAccess<o:p></o:p>
     <o:p></o:p>
  .MailMerge<o:p></o:p>
          .Destination = wdSendToNewDocument<o:p></o:p>
          .SuppressBlankLines = True<o:p></o:p>
          With .DataSource<o:p></o:p>
              .FirstRecord = wdDefaultFirstRecord<o:p></o:p>
              .LastRecord = wdDefaultLastRecord<o:p></o:p>
          End With<o:p></o:p>
          .Execute Pause:=False

<o></o>

 

[Kenneth Hobson]

You need to set the Open parameters like I did in this Word macro. Otherwise, the word doc will use the datasource that it was set to initially.

Sub MergeRun(frmFile As String, datFile As String, _
  Optional bClose As Boolean = True, Optional bPrint As Boolean = True, _
  Optional iNoCopies As Integer = 1)
  If Dir(frmFile) = "" Or Dir(datFile) = "" Then Exit Sub
  
 'On Error GoTo endnow
  Application.DisplayAlerts = wdAlertsNone
  
  'Open form file and associate data file
  Documents.Open frmFile, False, True, False
  ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
  ActiveDocument.MailMerge.OpenDataSource name:=datFile, _
    ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
    Format:=wdOpenFormatAuto, Connection:="", SQLStatement:="", SQLStatement1 _
    :="", SubType:=wdMergeSubTypeOther
  'Merge to a new document
  With ActiveDocument.MailMerge
      .Destination = wdSendToNewDocument
      .SuppressBlankLines = True
      With .DataSource
          .FirstRecord = wdDefaultFirstRecord
          .LastRecord = wdDefaultLastRecord
      End With
      .Execute Pause:=False
  End With
  
  If bPrint = True Then
    Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
      wdPrintDocumentContent, Copies:=iNoCopies, Pages:="", PageType:=wdPrintAllPages, _
      ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
      False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
      PrintZoomPaperHeight:=0
  End If
  
  If bClose = True Then
  ActiveDocument.Close False
  ActiveDocument.Close False
  End If
endnow:
  Application.DisplayAlerts = wdAlertsAll
End Sub

 

[snecz]

Should I put it in a Word and execute form Excel?
I don't know how to rewrite it...
Could you please help me?

 

[snecz]

Ok. It's working now.
But I have changed code in Excel

<link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CMISKIE%7E1%5CUSTAWI%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml>  <w:WordDocument>   <w:View>Normal</w:View>   <w:Zoom>0</w:Zoom>   <w:HyphenationZone>21</w:HyphenationZone>   <w:PunctuationKerning/>   <w:ValidateAgainstSchemas/>   <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>   <w:IgnoreMixedContent>false</w:IgnoreMixedContent>   <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>   <w:Compatibility>    <w:BreakWrappedTables/>    <w:SnapToGridInCell/>    <w:WrapTextWithPunct/>    <w:UseAsianBreakRules/>    <w:DontGrowAutofit/>   </w:Compatibility>   <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>  </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml>  <w:LatentStyles DefLockedState="false" LatentStyleCount="156">  </w:LatentStyles> </xml><![endif]--><style> <!--  /* Style Definitions */  p.MsoNormal, li.MsoNormal, div.MsoNormal     {mso-style-parent:"";     margin:0cm;     margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:12.0pt;     font-family:"Times New Roman";     mso-fareast-font-family:"Times New Roman";} pre     {margin:0cm;     margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Courier New";     mso-fareast-font-family:"Times New Roman";} @page Section1     {size:612.0pt 792.0pt;     margin:70.85pt 70.85pt 70.85pt 70.85pt;     mso-header-margin:35.4pt;     mso-footer-margin:35.4pt;     mso-paper-source:0;} div.Section1     {page:Section1;} --> </style><!--[if gte mso 10]> <style>  /* Style Definitions */  table.MsoNormalTable     {mso-style-name:Standardowy;     mso-tstyle-rowband-size:0;     mso-tstyle-colband-size:0;     mso-style-noshow:yes;     mso-style-parent:"";     mso-padding-alt:0cm 5.4pt 0cm 5.4pt;     mso-para-margin:0cm;     mso-para-margin-bottom:.0001pt;     mso-pagination:widow-orphan;     font-size:10.0pt;     font-family:"Times New Roman";     mso-ansi-language:#0400;     mso-fareast-language:#0400;     mso-bidi-language:#0400;} </style> <![endif]-->  
With docWD.MailMerge<o:p></o:p><u1:p></u1:p>
 .OpenDataSource Name:= _<o:p></o:p><u1:p></u1:p>
           "H:\Source\file.xls", ConfirmConversions:=False, ReadOnly:= _<o:p></o:p><u1:p></u1:p>
           False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _<o:p></o:p><u1:p></u1:p>
            PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _<o:p></o:p><u1:p></u1:p>
           "", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _<o:p></o:p><u1:p></u1:p>
           "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source= H:\Source\file.xls;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Typ" _<o:p></o:p><u1:p></u1:p>
           , SQLStatement:="SELECT * FROM `data$'`", SQLStatement1:="", _<o:p></o:p><u1:p></u1:p>
           SubType:=wdMergeSubTypeAccess<o:p></o:p><u1:p></u1:p>
  With docWD    <o:p></o:p><u1:p></u1:p>
  .MailMerge<o:p></o:p><u1:p></u1:p>
          .Destination = wdSendToNewDocument<o:p></o:p><u1:p></u1:p>
          .SuppressBlankLines = True<o:p></o:p><u1:p></u1:p>
          With .DataSource<o:p></o:p><u1:p></u1:p>
              .FirstRecord = wdDefaultFirstRecord<o:p></o:p><u1:p></u1:p>
              .LastRecord = wdDefaultLastRecord<o:p></o:p><u1:p></u1:p>
          End With<u1:p></u1:p><o:p></o:p>
            .Execute Pause:=False
End With

for

appWD.Run MacroName:="Makro1"

and I just run Makro1 in Word after opening .doc file.
<o></o>