Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
Default Merging to A, B,C...

I have no problem setting up my merge from Excel to Word onto an Envelope
for all the names I have in the spread sheet. What I would like to be able
to do is merge where it uses the first letter of the Last Name and be able
to merge where all records begin with A, then again for B and so on.

Or even the ability to select say 50 records, then records 51-100, then
101-150 and so on. Any help greatly appreciated. I'm using XP and Office
2007

--

Regards
Michael Koerner



  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Merging to A, B,C...

Some clarification please: can we assume that you know how to select
either the As (may be possible in the Query options facility, or you
might be able to add a SKIPIF field to your mail merge main document
that does something like

{ SKIPIF { MERGEFIELD "Last Name" } = "A*" }

then change the A to B, B to C to do each batch) or the record numbers?
If so, is it the automation part you want, i.e. you want to be able to
merge batch A (or 1-100), then, as a separate step, batch B, without
having to change the selection criteria?

Peter Jamieson

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

Michael Koerner wrote:
I have no problem setting up my merge from Excel to Word onto an Envelope
for all the names I have in the spread sheet. What I would like to be able
to do is merge where it uses the first letter of the Last Name and be able
to merge where all records begin with A, then again for B and so on.

Or even the ability to select say 50 records, then records 51-100, then
101-150 and so on. Any help greatly appreciated. I'm using XP and Office
2007

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
Default Merging to A, B,C...

Peter;

Thanks very much what you posted is what I'm looking for only I don't know
how to use what you posted.

The second part was to select the records in 100 record increments to create
the merge documents. Different than selecting from the Alpha characters.

--

Regards
Michael Koerner


"Peter Jamieson" wrote in message
...
Some clarification please: can we assume that you know how to select
either the As (may be possible in the Query options facility, or you
might be able to add a SKIPIF field to your mail merge main document
that does something like

{ SKIPIF { MERGEFIELD "Last Name" } = "A*" }

then change the A to B, B to C to do each batch) or the record numbers?
If so, is it the automation part you want, i.e. you want to be able to
merge batch A (or 1-100), then, as a separate step, batch B, without
having to change the selection criteria?

Peter Jamieson

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

Michael Koerner wrote:
I have no problem setting up my merge from Excel to Word onto an Envelope
for all the names I have in the spread sheet. What I would like to be able
to do is merge where it uses the first letter of the Last Name and be able
to merge where all records begin with A, then again for B and so on.

Or even the ability to select say 50 records, then records 51-100, then
101-150 and so on. Any help greatly appreciated. I'm using XP and Office
2007




  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Merging to A, B,C...

Can only get back to this in a day or two.

Peter Jamieson

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

Michael Koerner wrote:
Peter;

Thanks very much what you posted is what I'm looking for only I don't know
how to use what you posted.

The second part was to select the records in 100 record increments to create
the merge documents. Different than selecting from the Alpha characters.

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Merging to A, B,C...

You can try a macro like

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 = "Sheet1$"

' Set this to the exact name of the column containing the name
' (upper/lower case is probably significant
strColumnName = "mycolumn"

Set objMMMD = ActiveDocument
With objMMMD
For iLetter = Asc("A") To Asc("Z")
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [Sheet1$]" & _
" 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

See e.g. Graham Mayor's page at
http://www.gmayor.com/installing_macro.htm
if you need help on installing and running macros.

Peter Jamieson

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

Michael Koerner wrote:
Peter;

Thanks very much what you posted is what I'm looking for only I don't know
how to use what you posted.

The second part was to select the records in 100 record increments to create
the merge documents. Different than selecting from the Alpha characters.



  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
Default Merging to A, B,C...

Peter;

You macro to me looks like it is for a letter, will try it out, But, my need
is for envelopes, and has to have the following. this is taken from the
template file after it confirms the source of the data.

First Name Last Name
Address1
Address2
City Prov
Zip
Country

--

Regards
Michael Koerner


"Peter Jamieson" wrote in message
...
You can try a macro like

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 = "Sheet1$"

' Set this to the exact name of the column containing the name
' (upper/lower case is probably significant
strColumnName = "mycolumn"

Set objMMMD = ActiveDocument
With objMMMD
For iLetter = Asc("A") To Asc("Z")
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [Sheet1$]" & _
" 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

See e.g. Graham Mayor's page at
http://www.gmayor.com/installing_macro.htm
if you need help on installing and running macros.

Peter Jamieson

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

Michael Koerner wrote:
Peter;

Thanks very much what you posted is what I'm looking for only I don't know
how to use what you posted.

The second part was to select the records in 100 record increments to
create
the merge documents. Different than selecting from the Alpha characters.



  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Merging to A, B,C...

Mine isn't really for any particular type of document - the reason the
word "letter" appears is because it's doing one merge for each letter of
the alphabet. But the macro does assume that you have your mail merge
main document set up, with all the fields that you need, and that you
have already made a connection to the data source.

Peter Jamieson

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

Michael Koerner wrote:
Peter;

You macro to me looks like it is for a letter, will try it out, But, my need
is for envelopes, and has to have the following. this is taken from the
template file after it confirms the source of the data.

First Name Last Name
Address1
Address2
City Prov
Zip
Country

  #8   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
Default Merging to A, B,C...

Ahhhhhh! That shows you how much I know about macros g Will try it out,
thanks very much.

--

Regards
Michael Koerner


"Peter Jamieson" wrote in message
...
Mine isn't really for any particular type of document - the reason the
word "letter" appears is because it's doing one merge for each letter of
the alphabet. But the macro does assume that you have your mail merge
main document set up, with all the fields that you need, and that you
have already made a connection to the data source.

Peter Jamieson

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

Michael Koerner wrote:
Peter;

You macro to me looks like it is for a letter, will try it out, But, my
need
is for envelopes, and has to have the following. this is taken from the
template file after it confirms the source of the data.

First Name Last Name
Address1
Address2
City Prov
Zip
Country



  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
Default Merging to A, B,C...

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

--

Regards
Michael Koerner


"Peter Jamieson" wrote in message
...
You can try a macro like

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 = "Sheet1$"

' Set this to the exact name of the column containing the name
' (upper/lower case is probably significant
strColumnName = "mycolumn"

Set objMMMD = ActiveDocument
With objMMMD
For iLetter = Asc("A") To Asc("Z")
.MailMerge.DataSource.QueryString = _
" SELECT * FROM [Sheet1$]" & _
" 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

See e.g. Graham Mayor's page at
http://www.gmayor.com/installing_macro.htm
if you need help on installing and running macros.

Peter Jamieson

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

Michael Koerner wrote:
Peter;

Thanks very much what you posted is what I'm looking for only I don't know
how to use what you posted.

The second part was to select the records in 100 record increments to
create
the merge documents. Different than selecting from the Alpha characters.



  #10   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Merging to A, B,C...

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



  #11   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
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   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Michael Koerner Michael Koerner is offline
external usenet poster
 
Posts: 41
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?



Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
100% is merging as 1% Annette Mailmerge 3 March 10th 09 08:38 PM
MERGING Ransom Microsoft Word Help 3 February 22nd 08 07:32 AM
Merging Darlene Mailmerge 2 December 12th 07 11:11 AM
Getting 0's when merging thandy Mailmerge 3 December 19th 06 02:51 PM
Merging Chris Microsoft Word Help 1 May 9th 05 05:31 PM


All times are GMT +1. The time now is 11:43 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"