Looping through an Excel list to generate a dynamic Word document

**Richard Buttrey** · 03-22-2011, 10:01 AM

Hi,

Why do you need to generate a Word document? In my experience it's much easier to keep everything in Excel.
An Excel sheet can be set out to look like a typical word document. Most users I know invariably prefer to receive a finished document in Excel so that where necessary they can use it as the base for other tasks.

And if the ultimate output is a pdf file there's absolutely no need for Word.

Just a thought.....

Regards

**Nabla21** · 03-22-2011, 10:52 AM

Actually, I need a Word document because the document will be made of many information with a lot of text pages. My thought was to made it like I said, but there are actually many content in each section, with each section corresponding to only 1 line in the Excel.
The objective is to automate the creation of the word document using Excel preset document with only cells to complete to generate the Word document.

Thanks for your answer anyway

**Richard Buttrey** · 03-22-2011, 11:25 AM

Hi,

OK, but I still don't see why a Word doc is the only unique solution.

It would of course be possible to pick up a line of cells from Excel and with a macro use them to populate a paragraph or single row in Word, and even slot them into specific places.

But just because you have other text pages in the Word document I can see no reason why those self same text paragraphs could not exist in an Excel sheet, using word wrap and merged cells if necessary. There is no conceptual difference between something you see in a Word doc and something you see in an Excel sheet. The two can be made to look exactly the same.

Regards

**Nabla21** · 03-29-2011, 10:15 AM

Well, actually, I want to make a Word document in order to have a summary function, and so on. And the difficulty is that I need that summary to be dynamic, depending on the content of my Excel document.
Because as I said, the word document would be generated based on the content of the xls doc, which wouldn't be with the same number of line each time...

That's why I had in mind a "loop" to populate the Word document. I guess that a macro would perform this indeed, but my point is that I don't know where to start, as I never created any macros...

That's on this point that I'd really need help

Thanks anyway for your answer.

Regards

**snb** · 03-29-2011, 10:37 AM

You can use mailmerge in Word to assemble any kind of document, using the information in Excel.
Through the mailmergemethod you can establish a direct connection between the Word-document and the Excel workbook.
By using fields in Word you can import, introduce text conditionally on the values in Excel, etc.

To help you any further it would be wise to post an example Word-document and an example Excel workbook.

**Nabla21** · 03-30-2011, 04:05 AM

I tried to used mailmerge actually, but I was blocked because the generated document was not "dynamic" and the mailmerge function creates me 1 Word document for each line in the Excel document.
The fields were static, and that was not what I was looking for.

Here's the structure of my Excel file :
Phase ----- Phase Description ----- Phase Beginning date ----- Phase Ending date ----- Phase ressources

In addition to that, I would need to have a global field for the following fields :
Document Reference
Document Date
Read by (name of verificator)
Writed by (name of writer)

And this would create a Word composed of a title (Document Reference) a first page containing my document information (date, reader, writer, brief introduction), then a summary with the content of the document, which would be composed of :
I. Phase 1
--- Description Phase 1
--- Date Phase 1
--- Ressources Phase 1

II. Phase 2
--- Description Phase 2
--- Date Phase 2
--- Ressources Phase 2

...

And so on, depending on the number of line of the Excel document.

Did I made my point clear ? It's not easy to explain in english, as I'm french :P

Thanks anyway !

**snb** · 03-30-2011, 05:52 AM

In the attachment a Word document with a macro.

The only thing you have to change in the macro is the fullname of the Excel-file.
Then you can 'play' the macro and see the result in your document.
You can switch the fieldview using Alt-F9.

Awaiting your feedback.