Excel to Word

[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????

 

[Tom Ogilvy]

Add a sheet to your workbook that contains formula to build a database
structure with pertinent information such as name, address, scores,
assessments (pulled from your current sheet - again, emphasize the use of
formulas).

Then compose your base letter in word and use mailmerge.

 

[Chief S.]

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????

Yes, it's definitely possible. A simple approach is to use GetObject()
to set an object variable to the currently running instance of the
"other program". The example below calls two macros that I have to save
a bunch of Word and Excel files as text-only. In this case, the code
below resides in Word, so the "other program" is Excel.

'This sub resides in Word. It calls a Word macro and an Excel macro.
Sub ExampleWordAndExcel()
'Make sure Excel is running
On Error Resume Next
Set exc = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
MsgBox "Excel must be running first."
Exit Sub
End If
On Error GoTo 0

'Call the Work macro.
WordSaveAsText

'Call an Excel macro.
exc.Run ("'Personal.xls'!ExcelSaveAsText")

'Not sure why you have to do this, but I've been told
'it's a good idea.
Set exc = Nothing
End Sub

The broader point for you is that once you have access to the object
variable (exc in example above), you have access to the entire range of
functionality that you're used to using when you normally write VB code
in that application. For example, if we wanted the Word code above to do
other stuff in Excel, we use the normal syntax, plus the exc object
variable that we've created:
v = exc.Cells(10,3).Value 'Grab the value from a cell
exc.Rows(3).Delete 'Delete a row
etc.

Once you have the object variable (exc), the sky's the limit.

 

[That's Confidential]

FAO: CHIEF

Chief, I ain't got much of a clue what you mean by all that as I haven't got
the foggiest what you mean.

If you're willing to help me out more, let me know in this forum, and i'll
post my email address and then you can send me a blank email. Think it would
be a lot easier by email!

Thanks

 

[Tom Ogilvy]

Just out of curiosity, why do you want to go for a complex programming
solution (which you say you know nothing about) when this can be easily
handled with built in capabilities and no code. You could have this solved
in an hour.

--
Regards,
Tom Ogilvy

FAO: CHIEF

Chief, I ain't got much of a clue what you mean by all that as I haven't got
the foggiest what you mean.

If you're willing to help me out more, let me know in this forum, and i'll
post my email address and then you can send me a blank email. Think it would
be a lot easier by email!

Thanks

for