Problems with VBA mailmerge

M

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
 
W

witek

mkz said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top