Mailmerge: <<First Record>> in stead of <<Next Record>>

[Guus]

Hello,
I have a mailmerge document, merging with Exceldata, which start with a
summary of scores on the first page. The following pages show the
detail-information for every recipient on a own page.

1. On the summary (first page) a print the final score; then I skip to the
following recipient with the Wordfield <<Next Record>>
E.g. 10 rows will be filled with information when I finish the mailmerge.

2. The Second page and following show all the subscores of every recipient;
this information uses ONE page for EACH recipient.

MY PROBLEM IS:
The second page I want to start (again) on the first record, because in the
summary I have reached the last record.

So, I want to INSERT MERGEFIELD
- <<First Record>>, so the opposite of <<Next Record>>

How can I fix this problem?
Thank you in advance.
Guus

 

[Peter Jamieson]

Frankly, the simplest approach is to do the following
a. set up a "catalog/directory" type merge that creates your first page.
You will probably just need one line on it with merge fields, plus one extra
paragraph mark. You shouldn't need any <<Next record>> fields
b. set up a second "Letter" type merge that produces one page of output per
"recipient"
c. perfrom each merge to an output document
d. insert one output document at the ned of the other using File|Insert
e. add any other trim you need.

If you need to do that regularly, I'd suggest that it's simpler to get the
above process right, then record/edt a smalll macro to automate it, than to
try to do it all in a single merge.

An alternative for step (a) may be to enable the Database toolbar in Word
(Tools|Customize) then use the Insert Database icon to insert the data
(perhaps as a field, so it's easy to repeat if you need to). But your
control over the table layout is fairly limited.

Peter Jamieson

 

[Guus]

Thank you sor far,
I understand that it is easier to merge both documents separately and after
the merge, put the results together. Maybe the answer to the next question
will help me to decide if automating is an option or not:

1. I want to show an Inputbox to read a variable.
This variable is exactly to name of the Excelsheet.
2. I have created a macro to do this for me,
but it gives me an error-message of 'Read-Only'.

This is the source of the macro I tried to create, but which give me errors:
Sub MergeFeedbackDocument()
Dim Code As String
Code = InputBox("Geef your code (e.g. B13R074)", _
"Merge Feedback Document")

Documents.Open _
FileName:="D:\Template_Totals.doc", _
ConfirmConversions:=True, _
Format:=wdOpenFormatAuto

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.Name = "D:\Excelsheets\Feedback_" & Code & ".xls"
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

Windows("D:\Template_Totals.doc").Close savechanges:=False

'Create a new section at a new page for the Detailed Feedback
Selection.EndKey Unit:=wdStory
Selection.InsertBreak Type:=wdSectionBreakNextPage
With Selection.PageSetup
.Orientation = wdOrientLandscape
.TopMargin = CentimetersToPoints(0.5)
.BottomMargin = CentimetersToPoints(0.5)
.LeftMargin = CentimetersToPoints(1.5)
.RightMargin = CentimetersToPoints(1.5)
.HeaderDistance = CentimetersToPoints(0.4)
.FooterDistance = CentimetersToPoints(0.4)
.PageWidth = CentimetersToPoints(29.7)
.PageHeight = CentimetersToPoints(21)
.SectionStart = wdSectionNewPage
End With

Documents.Open FileName:="D:\Template_Details.doc", _
ConfirmConversions:=True, _
Format:=wdOpenFormatAuto

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.Name = "D:\Excelsheets\Feedback_" & Code & ".xls"
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^b"
.Replacement.Text = "^m"
.Forward = True
.Wrap = wdFindContinue
End With
Selection.Find.Execute Replace:=wdReplaceAll

Selection.EndKey Unit:=wdStory
Selection.MoveLeft Unit:=wdCharacter, Count:=1
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.HomeKey Unit:=wdStory, Extend:=wdExtend
Selection.Copy
ActiveDocument.Close

Selection.PasteAndFormat (wdPasteDefault)
Selection.HomeKey Unit:=wdStory
Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Count:=1, Name:=""
Selection.Find.ClearFormatting
With Selection.Find
.Text = "^b"
.Replacement.Text = "^m"
.Forward = True
.Wrap = wdFindContinue
End With

If ActiveWindow.View.SplitSpecial <> wdPaneNone Then
ActiveWindow.Panes(2).Close
End If
If ActiveWindow.ActivePane.View.Type = wdNormalView Or ActiveWindow. _
ActivePane.View.Type = wdOutlineView Then
ActiveWindow.ActivePane.View.Type = wdPrintView
End If
ActiveWindow.ActivePane.View.SeekView = wdSeekCurrentPageHeader
ActiveWindow.ActivePane.View.SeekView = wdSeekMainDocument
Selection.EndKey Unit:=wdStory, Extend:=wdExtend
With Selection.PageSetup
.LineNumbering.Active = False
.Orientation = wdOrientLandscape
.HeaderDistance = CentimetersToPoints(0.4)
.FooterDistance = CentimetersToPoints(0.4)
.PageWidth = CentimetersToPoints(29.7)
.PageHeight = CentimetersToPoints(21)
End With
Selection.HomeKey Unit:=wdStory
ActiveDocument.SaveAs _
FileName:="D:\Feedback_" & Code & ".doc", _
FileFormat:=wdFormatDocument
End Sub

Thank you for further help,
Guus

 

[Doug Robbins - Word MVP]

If you click on Debug, what line of code is highlighted?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

I think you will need to do at least

ActiveDocument.MailMerge.OpenDataSource _
Name:= "D:\Excelsheets\Feedback_" & Code & ".xls"

(i.e I don't think setting a new name will be enough)

To make sure that Word opens the data soruce successfully you will probably
need to do at least

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
' Set the mail merge type you need
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters

ActiveDocument.MailMerge.OpenDataSource _
Name:= "D:\Excelsheets\Feedback_" & Code & ".xls"

You may also need to take account of the following article:
http://support.microsoft.com/kb/825765

Peter Jamieson

 

[Guus]

Hello,
It simply looks IMPOSSIBLE to change the datasource name.
Further, I still believe the VBA-command has to be something like:

ActiveDocument.MailMerge.OpenDataSource.Name:= _
"D:\Excelsheets\Feedback_" & Code & ".xls"

and NOT:
ActiveDocument.MailMerge.OpenDataSource _
Name:= "D:\Excelsheets\Feedback_" & Code & ".xls"

 

[Peter Jamieson]

Further, I still believe the VBA-command has to be something like:

ActiveDocument.MailMerge.OpenDataSource.Name:= _
"D:\Excelsheets\Feedback_" & Code & ".xls"

Why? OpenDataSource is a method call.

ActiveDocument.MailMerge.OpenDataSource _
Name:= "D:\Excelsheets\Feedback_" & Code & ".xls"

Did you try it?

Was it the syntax you didn't like? If so, use

ActiveDocument.MailMerge.OpenDataSource Name:= "D:\Excelsheets\Feedback_"
& Code & ".xls"

Alternatively you may need a connection string and a SQLStatement parameter
as well. But which version of Word ?

Peter Jamieson