Excel producing Word report

  • Thread starter Thread starter Laurent
  • Start date Start date
L

Laurent

What would be the most easiest way, on a dummy-proof user point of view, to
produce new Word documents reports from data produced and maintaned by
Excel ?

Here is the situation. We have a diagnostic tool developped with Excel and
used in academic context by our teachers to evaluate verbal and non verbal
communication by students. For each student, the system produce a
personnalized lenghty report. The system works pretty well, but the output
is still semi-automatic,

In the present system, for each student report, the teacher has to manually
adjust height of cells of the "report" zone (wording copied and displayed
in zone's cells vary in lenght, depending of evaluation and comments added
by the teacher), then copy the cells and paste them manually in a new Word
document created from a dedicated template. Then, the report can be
printed, saved and sent by email to the student in universal Word format.

Some of our teachers are not computer litterate. We would like to have a
single button action in Excel that could automatically create, format, save
and send by email any number of new Word document without manual
operations.

Is this easily doable ?

I suspect VBA coding will be required and I can handle that. I just need a
starting point to do that.

Thanks a lot
 
Laurent,

You can use a macro. Set a reference to the Word application using the
VBEditor's Tools | Refernces..., then you can run something like the macro
below, which will export range A1:A10 of the activesheet, with each cell's
value as a separate paragraph (with a blank line between each paragraph),
saved to a new file (name selected by the user, with a default of "Exported
report.doc") and saved by default into the same folder as the current Excel
file.

You can change any of those things fairly easily. And the bells and whistles
that you can add... well, they're limited only by your imagination.
I suspect VBA coding will be required and I can handle that. I just need a
starting point to do that.
Click to expand...

Good. But if you need help, post back and somebody will be around to lend a
hand.....This should get you started.

HTH,
Bernie
MS Excel MVP

Sub ExportToWord()

Dim oWord As Object
Dim oDoc As Object
Dim myCell As Range
Dim strFileName As String

Set oWord = CreateObject("word.application")

Set oDoc = oWord.Documents.Add

oWord.Visible = True
With oWord.Selection
For Each myCell In Range("A1:A10")
.TypeText myCell.Value
.TypeParagraph
.TypeParagraph
Next myCell
End With

oWord.ChangeFileOpenDirectory ThisWorkbook.Path
Application.Visible = True
Application.ScreenUpdating = True
strFileName = Application.GetSaveAsFilename("Exported report.doc")
oDoc.SaveAs strFileName
Set oDoc = Nothing
Set oWord = Nothing
End Sub
 
in
microsoft.public.excel.misc
Laurent,

You can use a macro. Set a reference to the Word application using
the VBEditor's Tools | Refernces..., then you can run something like
the macro below, which will export range A1:A10 of the activesheet,
with each cell's value as a separate paragraph (with a blank line
between each paragraph), saved to a new file (name selected by the
user, with a default of "Exported report.doc") and saved by default
into the same folder as the current Excel file.
Click to expand...

Thanks a lot Bernie. This is a very good starting point.VBA is easy to
learn and code can be improve gradually once the basic works. Your code
allows me to go forward. Thanks a lot.
Good. But if you need help, post back and somebody will be around to
lend a hand.....This should get you started.
Click to expand...

Thanks again. If I'm really stuck and can't find myself how to tackle an
issue, I won't hesitate to post back.

Regards


Laurent
 
Back
Top