School Reports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CS - 02 May 2005 13:58 GMT

Hi - I am trying to mail merge some school reports from our Access Database.
We have two tables Grades (10 records per pupil) and Attendance (1 record
per pupil).

Firstly I merge the Grades table and then separately merge the attendance
table to create 2 word files.

What I would like to do is to take their attendance merge file and insert
single pages into the relevant place in their Grades merge, but I can't
think of any way to do it apart from after it has all printed, counting 10
pages then inserting the relevant cover sheet by hand and so on...

Anyone have any suggestions - I had a look at the splitter code and was
thinking if I could get it to split my main doc every ten pages or
something?

Thanks,

Colin

Reply to this Message

Doug Robbins - 02 May 2005 16:18 GMT

It would be possible to use a modification of the splitter code to create a
new series of documents that was a combination of the output of the two
merges.

I am assuming here that the splitter code is the following:

Sub splitter()

' splitter Macro

' Macro created by Doug Robbins to save each letter created by a mailmerge
as a separate file.

Dim i As Long, Source as Document, Target as Document, Letter as Range
Set Source = ActiveDocument
For i = 1 to Source.Sections.Count
Set Letter = Source.Sections(i).Range
Letter.End=Letter.End-1
Set Target = Documents.Add
Target.Range=Letter
Target.SaveAs FileName:="Letter" & i
Target.Close
Next i

End Sub

Post back to the NG if you get stuck with the modifications.

Signature

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

> Hi - I am trying to mail merge some school reports from our Access
> Database. We have two tables Grades (10 records per pupil) and Attendance
[quoted text clipped - 15 lines]
>
> Colin

Reply to this Message

Bill - 02 May 2005 18:46 GMT

To be honest I have never used macros in word! I tried incrementing the i
by 10 each time but wasn't all that successful!

Any other tips?

Thanks,

Colin

> It would be possible to use a modification of the splitter code to create
> a new series of documents that was a combination of the output of the two
[quoted text clipped - 43 lines]
>>
>> Colin

Reply to this Message

Doug Robbins - 03 May 2005 08:36 GMT

I haven't tested this, but if you save the document created by merging with
the information on Grades as a file name Grades, and that created by merging
with the information on Attendances as a file named Attendance, both of them
in the default document folder, and then run the following version of the
macro when both of those documents are closed, it should open them up and
create a series of letters (Letter1, Letter21, Letter31 etc) each of which
will contain the 10 Grades pagess for the student, followed by the
Attendance page. Of course the order of the students in the merge datafiles
will need to be the same to ensure that the correct Attendance page goes
with each set of Grades pages.

Dim i As Long, Grades As Document, Attendance As Document, Target As
Document, Letter As Range
Set Grades = Documents.Open("Grades.doc")
Set Attendance = Documents.Open("Attendance.doc")
For i = 1 To Grades.Sections.Count Step 10
Set Letter = Grades.Sections(i).Range
Letter.End = Grades.Sections(10).Range.End - 1
Set Target = Documents.Add
Target.Range = Letter
Set Letter = Attendance.Sections.First.Range
Letter.End = Letter.End - 1
Target.Range.InsertAfter Letter
Attendance.Sections.First.Range.Delete
Target.SaveAs FileName:="Letter" & i
Target.Close
Next i

Signature

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

> To be honest I have never used macros in word! I tried incrementing the i
> by 10 each time but wasn't all that successful!
[quoted text clipped - 53 lines]
>>>
>>> Colin

Reply to this Message

Colin - 03 May 2005 22:31 GMT

Thanks for giving it a go! But it seems to mess the formatting around quite
a bit as I have used tables to merge data into and the copying and pasting
bit of the code seems to leave out the formatting...

What does letter do in the code - is letter a page?

Thanks,

Colin

>I haven't tested this, but if you save the document created by merging with
>the information on Grades as a file name Grades, and that created by
[quoted text clipped - 81 lines]
>>>>
>>>> Colin

Reply to this Message

Doug Robbins - 04 May 2005 07:20 GMT

Try the following modification that makes use of the .FormattedText property
of a range (which is what Letter is defined as)

' splitter Macro
' Macro created by Doug Robbins to save each letter created by a mailmerge
as a separate file.
Dim i As Long, Grades As Document, Attendance As Document, Target As
Document, Letter As Range
Set Grades = Documents.Open("Grades.doc")
Set Attendance = Documents.Open("Attendance.doc")
For i = 1 To Grades.Sections.Count Step 10
Set Letter = Grades.Sections(i).Range.FormattedText
Letter.End = Grades.Sections(10).Range.End - 1
Set Target = Documents.Add
Target.Range.FormattedText = Letter
Set Letter = Attendance.Sections.First.Range
Letter.End = Letter.End - 1
Target.Range.InsertAfter Letter
Attendance.Sections.First.Range.Delete
Target.SaveAs FileName:="Letter" & i
Target.Close
Next i
End Sub

Signature

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

> Thanks for giving it a go! But it seems to mess the formatting around
> quite a bit as I have used tables to merge data into and the copying and
[quoted text clipped - 91 lines]
>>>>>
>>>>> Colin

Reply to this Message