genreate ms-word doc from excel

[shishi]

Hi all,


I have worked on an excel project recently with a lot of help form
this board. The goal was to assort couple of tables from one sheet to
another to make things easier for comparison. Now I have a new task. I
need to generate couple of ms-word documents from this excel sheet. The
plan right now is to have a menuitem like "Generate docs" in excelsheet
and by clicking on that we can generate about 40 word documents or so.
We have a word template file. For each word document we need to have
the folowing things
1. Module name as the heading
2. Description of the module
3. Table of data
All these information are in the excel sheet. They are stored in excel
sheet using named ranges. Now, I need to gather these data and generate
the word docs. What would be the best approach to solve this problem.
Ideally we would like to do this with out even opening the word
application. I am requesting your valued suggestions on this problem.


Thanks in advance,
shishi

 

[Helmut Weber]

Hi Shishi,

read this first:

http://word.mvps.org/faqs/interdev/ControlWordFromXL.htm

Plus this example:

' -------
Dim l As Long
Dim oWrd As Word.Application
Dim oDoc As Word.Document
Set oWrd = New Word.Application
oWrd.Visible = False
For l = 1 To 40
Set oDoc = oWrd.Documents.Add
' see word help for arguments for add
oDoc.Range.InsertBefore Range("myCell").Value
oDoc.SaveAs "c:\test\" & Format(l, "0000") & ".doc"
oDoc.Close
Next
oWrd.Quit
Set oDoc = Nothing
Set oWrd = Nothing
' -----

Greetings from Bavaria, Germany
Helmut Weber, MVP WordVBA
"red.sys" & chr(64) & "t-online.de"
Word 2002, Windows 2000

 

[shishi]

Hi Helmut,

Thanks a lot for the best answer I received on this problem. I am now
able to gernerate the word documents. But still there is a problem for
which I am seeking some advice. The word report that we need to
generate has some specific style and layout. The layout is as shown
below.

Header1: company name on the left header2: project name on the right

Then comes the module name as heading

Then comes module description.

Then comes Table of Data.

Then comes footer with page number at the center and version number at
the right corner.

Now we need to have the flexibility of changing the header2(project
name), module name, module description and table. So how should we
approach this problem? Should we generate a word template file and
then use bookmarks or use ranges to generate word documents
programatically.

 

[Helmut Weber]

Hi Shishi,

I'd use a template with bookmarks, preferably inclusive [bookmarks],
which reside in paragraphs of the appropriate style.

Though this seems to be a task
which needs more assistance
than some postings in a newsgroup can provide.


Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"