Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
100% is merging as 1% | Mailmerge | |||
MERGING | Microsoft Word Help | |||
Merging | Mailmerge | |||
Getting 0's when merging | Mailmerge | |||
Merging | Microsoft Word Help |