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>

 

[Andrew Poulsom]

I don't know what's wrong, but I would compare your code with a macro recorded in word.

 

[snecz]

I don't know what's wrong, but I would compare your code with a macro recorded in word.

That's the code I recorded in Word first. It works fine in Word.

 

[Andrew Poulsom]

Are you sure this is right?

Set docWD = appWD.documents.Open(file.xls)

Shouldn't you be opening a word document?

 

[snecz]

Are you sure this is right?

Set docWD = appWD.documents.Open(file.xls)

Shouldn't you be opening a word document?

You're right but my mistake was in copying code. I've changed names of files so the code will be more clear. Yes, it should be file .doc.
And I have .doc in my original code...So that's not a problem - unfortunately.

 

[Andrew Poulsom]

What does your recorded code look like?

 

[snecz]

What does your recorded code look like?

<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]-->  Sub Makro1()<o:p></o:p>
  ActiveDocument.MailMerge.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>
      With ActiveDocument.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:p></o:p>
      End With<o:p></o:p>
    <o:p></o:p>
  End Sub

 

[Andrew Poulsom]

If that works the only difference I see is that:

With ActiveDocument.MailMerge

is lower down. What if you use?

docWD.MailMerge<O:P></O:P>.OpenDataSource Name:= _<?XML:NAMESPACE PREFIX = O /><O:P></O:P>

Also the next line .MailMerge seems superfluous.

 

[snecz]

I have changed

With docWD.MailMerge<o></o>.OpenDataSource Name

for

docWD.MailMerge.OpenDataSource Name:=

but that doesn't change anything and still doesn't work

 

[Andrew Poulsom]

I don't think I can help any more. Did you do anything with the ActiveDocument before you recorded your macro, like:

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters