Get the same error. The Sheet Name is Nominal Roll, and the column name is
LastName. Here is what you sent with your changes incorporated.
Sub OneMergePerInitialLetter()
' error trapping to be added
Dim iLetter As Integer
Dim objMMMD As Word.Document
Dim strSheetName As String
Dim strColumnName
' Set this to the name of the worksheet or to the range name
strSheetName = "Nominal Roll$"
' Set this to the exact name of the column containing the name
' (upper/lower case is probably significant
strColumnName = "LastName"
Set objMMMD = ActiveDocument
With objMMMD
For iLetter = Asc("A") To Asc("Z")
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(t) like '" & Chr(iLetter) & "%'"
With .MailMerge
' remove or change this as necessary
.Destination = wdSendToNewDocument
.Execute Pause:=False
End With
If MsgBox("Completed Letter " & Chr(iLetter) & _
". Do the next letter?", vbOKCancel) = vbCancel Then
Exit For
End If
Next
End With
Set objMMMD = Nothing
End Sub
--
Regards
Michael Koerner
"Peter Jamieson" wrote in message
...
Sorry, I changed one part of it and not the other...
Change this statement
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [Sheet1$]" & _
" WHERE ucase(t) like '" & Chr(iLetter) & "%'"
to
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(t) like '" & Chr(iLetter) & "%'"
As far as I can tell, it is this statement that is going wrong, so if
that does not fix the problem, it is possible that the sheet name that
you specify in the earlier line
strSheetName = "Sheet1$"
is not quite right. If the sheet name in the tab at the bottom of the
sheet in Excel is "Sheet1", you have to add a $ sign at the end and use
strSheetName = "Sheet1$"
not
strSheetName = "Sheet1"
If you are using a range name rather than a sheet name, /do not/ append
a $ sign.
Peter Jamieson
http://tips.pjmsn.me.uk
Visit Londinium at
http://www.ralphwatson.tv
Michael Koerner wrote:
Peter;
Tried you macro. Keep getting a Run-time error '4198' Command failed. I
have
a hard time spelling macro, let alone editing them g