Looping through an Excel list to generate a dynamic Word document

**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.

**Nabla21** · 03-30-2011, 08:52 AM

You're macro is a very good beggining to help me.
I still have one more question about this :
- How would I apply a "style" to the content of a cell ? (like in your macro, apply title_1 style to the sq(j,1) for instance)

Thank you very much for your fast answer :-D

**snb** · 03-30-2011, 09:25 AM

In the example file I put the whole content of sheet1 into 1 Word-field.
Since I have no idea how you want to format the distinct elements I can't give a simple solution.
It would be nice to post an example of the Excel sheet and of the layout in your Word-document.