A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Word » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Merging to A, B,C...



 
 
Thread Tools Display Modes
  #11  
Old September 8th, 2009, 03:52 PM posted to microsoft.public.word.mailmerge.fields
Michael Koerner
external usenet poster
 
Posts: 2,341
Default Merging to A, B,C...

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



  #12  
Old September 8th, 2009, 04:16 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Merging to A, B,C...

Sorry Michael - haevn't really been paying attention here.

With any luck, the following modification to that statement
should do it:

..MailMerge.DataSource.QueryString = _
" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(" & strColumnName & ") like '" & Chr(iLetter) & "%'"

" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(t) like '" & Chr(iLetter) & "%'"


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Michael Koerner wrote:
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




  #13  
Old September 8th, 2009, 06:37 PM posted to microsoft.public.word.mailmerge.fields
Michael Koerner
external usenet poster
 
Posts: 2,341
Default Merging to A, B,C...

It does, and thank you very much. Can the macro be changed so that I am
asked which letter of the alphabet to start with?

--

Regards
Michael Koerner


"Peter Jamieson" wrote in message
...
Sorry Michael - haevn't really been paying attention here.

With any luck, the following modification to that statement
should do it:

..MailMerge.DataSource.QueryString = _
" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(" & strColumnName & ") like '" & Chr(iLetter) & "%'"

" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(t) like '" & Chr(iLetter) & "%'"


Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Michael Koerner wrote:
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






  #14  
Old September 8th, 2009, 07:49 PM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,550
Default Merging to A, B,C...

You can try

Sub OneMergePerInitialLetterAskStart()
' error trapping to be added
Dim bDone As Boolean
Dim iLetter As Integer
Dim objMMMD As Word.Document
Dim strColumnName As String
Dim strSheetName As String
Dim strStartLetter As String
bDone = False
Do
strStartLetter = InputBox("Enter the starting letter," & _
" or blank to quit", "Starting letter", "a")
strStartLetter = UCase(Trim(strStartLetter))
If Len(strStartLetter) = 0 Then
bDone = True
Else
If Len(strStartLetter) 1 Then
MsgBox "Enter a single letter" & _
" (from A to Z or a to z)," & _
" or blank to quit", vbOKOnly
Else
If strStartLetter "A" _
Or strStartLetter "Z" Then
MsgBox "Enter a (single) letter" & _
" from A to Z or a to z," & _
" or blank to quit", vbOKOnly
Else
bDone = True
End If
End If
End If
Loop Until bDone

If strStartLetter "" Then
' 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(strStartLetter) To Asc("Z")
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(" & strColumnName & ") like '" & _
Chr(iLetter) & "%'"
With .MailMerge
' remove or change this as necessary
.Destination = wdSendToNewDocument
On Error GoTo norecords
.Execute Pause:=False
End With
If MsgBox("Completed Letter " & Chr(iLetter) & _
". Do the next letter?", vbOKCancel) = vbCancel Then
Exit For
End If
norecords:
If Err.Number = 5631 Then
' Assume it is because there were no records for this letter
' Not necessarily true - could be just a badly formed query
Err.Clear
On Error GoTo 0
Resume atloop
Else
' just stop
On Error GoTo 0
End If
atloop:
Next
End With
Set objMMMD = Nothing
End If
End Sub



Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Michael Koerner wrote:
It does, and thank you very much. Can the macro be changed so that I am
asked which letter of the alphabet to start with?

  #15  
Old September 8th, 2009, 08:24 PM posted to microsoft.public.word.mailmerge.fields
Michael Koerner
external usenet poster
 
Posts: 2,341
Default Merging to A, B,C...

Peter;

This is so Cool. Thank you very much. Makes ones life just a little bit
easier.

--

Regards
Michael Koerner


"Peter Jamieson" wrote in message
...
You can try

Sub OneMergePerInitialLetterAskStart()
' error trapping to be added
Dim bDone As Boolean
Dim iLetter As Integer
Dim objMMMD As Word.Document
Dim strColumnName As String
Dim strSheetName As String
Dim strStartLetter As String
bDone = False
Do
strStartLetter = InputBox("Enter the starting letter," & _
" or blank to quit", "Starting letter", "a")
strStartLetter = UCase(Trim(strStartLetter))
If Len(strStartLetter) = 0 Then
bDone = True
Else
If Len(strStartLetter) 1 Then
MsgBox "Enter a single letter" & _
" (from A to Z or a to z)," & _
" or blank to quit", vbOKOnly
Else
If strStartLetter "A" _
Or strStartLetter "Z" Then
MsgBox "Enter a (single) letter" & _
" from A to Z or a to z," & _
" or blank to quit", vbOKOnly
Else
bDone = True
End If
End If
End If
Loop Until bDone

If strStartLetter "" Then
' 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(strStartLetter) To Asc("Z")
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [" & strSheetName & "]" & _
" WHERE ucase(" & strColumnName & ") like '" & _
Chr(iLetter) & "%'"
With .MailMerge
' remove or change this as necessary
.Destination = wdSendToNewDocument
On Error GoTo norecords
.Execute Pause:=False
End With
If MsgBox("Completed Letter " & Chr(iLetter) & _
". Do the next letter?", vbOKCancel) = vbCancel Then
Exit For
End If
norecords:
If Err.Number = 5631 Then
' Assume it is because there were no records for this letter
' Not necessarily true - could be just a badly formed query
Err.Clear
On Error GoTo 0
Resume atloop
Else
' just stop
On Error GoTo 0
End If
atloop:
Next
End With
Set objMMMD = Nothing
End If
End Sub



Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

Michael Koerner wrote:
It does, and thank you very much. Can the macro be changed so that I am
asked which letter of the alphabet to start with?



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.