Excel and Word together

[That's Confidential]

I am currently in the process of putting together a spreadsheet which will
assess students progress (as I am a teacher.) There are 5 sections to my
spreadsheet, with 5 cells belonging to each section. Each of these 5 cells
represents a test within that section and the pupils score in that test.
Once the 5 tests have been completed, the spreadsheet then works out an
average for that section for each specific pupil.

My question is, I would like an MS Word document to create a report for that
specific pupil. I would like the MS Word document to actually look at the
results in the spreadsheet (ie the date in specific cells) and then create a
report for each specific pupil, as I will have entered such info such as, if
H2 = <90%, then write "(NAME) has been an excellent pupil this year, with
his grades ranging from (lowest grade) to (highest grade!)" etc etc....... I
would also like the MS Word document to replace certain aspects of the
report, such as at the beginning of sentences, with each pupils name. I
believe that this is possible!

Anyone any idas on where to start????

 

[Harald Staff]

Hi

A good starting point for automating like this is
http://www.erlandsendata.no/english/index.php?t=envbaole
"Automating Excel with Word" and vice versa.

 

[Arvi Laanemets]

Hi

You must have a table on first sheet (NB! 1st sheet!) of your workbook, with
a row for every student. And the table MUST have a header row too.
In this table, all info for particular student must be on single row.
So the table will be something like this:
Name LowGrade HighGrade AvgGrade ...
John 3 5 4
....

Probably fields in this table are filled automatically depending entries on
other workbooks.
Then you must save the workbook before to continue.

Open MS Word, Select Tools.MailMerge, and create Main Document as Form
Letter.
Click on Get Data button, select Open Data Source, and open your Excel
Workbook (you must set file type to *.xls). Select 'Entire Spreadsheet', and
then 'Edit Main Document'.

Now create your report. Enter all permanent texts directly into document.
You can insert values from your Excel table into any location in mail merge
document using 'Insert Merge Field' button on Mail Merge toolbar - width of
inserted fields is adjusted automatically. You also can insert date or time
etc. fields using 'Insert Word Field' button. You can use Word's formatting
features, and/or insert tables etc.

After you hve designed your report, select Tools.MailMerge.Merge
In 'Merge to' field you can select your output destination (new document,
printer, or e-mail).
Query Options button takes you to form, where you can set filter conditions
for your output (p.e. Name='John') and output order.
Or you can define the records range to be merged (p.e. rows 3-5).
When all needed options are set, press Merge button.
It's all.

Save your merge document (I mean main document, when you selected new
document as destination, then you have it too - you decide yourself wht you
do with it) - I advice to use a name which allows to recognize it as merge
document later. Once created, the mail merge document is reusable, until
your data source (your Excel table) exists and is available.