Problems with VBA mailmerge

[mkz]

Hi,

I am having some problems getting a VBA to work. It's supposed to ope
up a mailmerge template and fill it in from an excel sheet, which it ha
already been linked to, then print the sheets. Here's my code:


Dim wd As Object
Dim wdocSource As Object

Dim strWorkbookName As String

On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0

Set wdocSource = wd.Documents.Open("X:\y\z\a.doc")

strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

wdocSource.MailMerge.MainDocumentType = wdFormLetters

wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read"
_
SQLStatement:="SELECT * FROM `sheet$` WHERE `Method`='xyz
And `Amount:` >0"

With wdocSource.MailMerge
.Destination = wdSendToPrinter
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

wd.Visible = True
wdocSource.Close SaveChanges:=False

Set wdocSource = Nothing
Set wd = Nothing

End Sub


I get the error "Run-Time error 4198: command failed." When i clic
debug it takes me to this section:

wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read"
_
SQLStatement:="SELECT * FROM `sheet$` WHERE `Method`='xyz
And `Amount:` >0"


any ideas what could be wrong? I used this macro in another spreadshee
and it worked just fine.

Thanks so much

 

[witek]

Connection:="Data Source=" & strWorkbookName & ";Mode=Read",

I did not try that but I am not sure if that is valid connection string.
Try to google some examples or setup connection manually and read
connection property.