Best Way to Populate Word

[mmcdonald6]

Hello all. I am trying to populate Word from Excel and there seems to be two ways to go about doing this, Mail Merge or VBA.
I was wondering which of the two is the best to further pursue for my application.

I am looking for a solution which populates User Requirements documents. These requirements, however, can vary in their contents and fields depending on the equipment being specified.
Here is a general example of the format I use:
[h=1]1.0 Requirement Category 1[/h]

1.1.1 Must do x
1.1.2 Must do y
[h=2]1.2 Requirement 2[/h]
1.2.1 Must do a
1.2.2 Must do b

<tbody>
[TD="colspan: 2"][h=2]1.1 Requirement 1[/h][/TD]

</tbody>

[h=1]2.0 Requirement Category 2[/h]

2.1.1 Must do x
2.1.2 Must do y

<tbody>
[TD="colspan: 2"][h=2]2.1 Requirement 1[/h][/TD]

</tbody>

My goal is this:

• The entire contents of all text would be entered in Excel.
• The Word document would populate the fields based on that data.

Whatever the approach, it needs to be able to accommodate any number of requirements

• A document could have as little as 3 requirement categories up to 20 or 30
• A requirement category could have as few as 1 item up to 20

I know this can be done in VBA, and I am somewhat familiar with VBA, but am looking for the best approach. That is, if VBA is correct, should the script be in Word or Excel?

So, I was looking to you guys for opinions on the two approaches or direction to relevant links or tutorials.

Thank you in advance.

 

[Lee.]

I usually use VBA and Bookmarks in Word when transferring data from Excel to a specific document/report in Word. For example below

Sub TemplateDoc()
Dim wdApp As Object 'Word.Application
Dim wdDoc As Object 'Word.Document
On Error Resume Next
  Set wdApp = CreateObject("Word.Application")
  wdApp.Visible = True
  Set wdDoc = wdApp.Documents.Add(Template:=ActiveWorkbook.path & "\WordTemplate.dot")


wdDoc.Bookmarks("Bookmark1").Range.Text = Worksheets("Sheet1").Range("A2").Text
wdDoc.Bookmarks("Bookmark2").Range.Text = Worksheets("Sheet1").Range("A3").Text
etc
end sub

 

[Macropod]

Mailmerge and VBA aren't the only methods. You can also link a Word document to an Excel workbook. Which method might be appropriate depends on what you are trying to do and how your Excel data are structured. It's also not even apparent why you're keen to involve Excel at all, since what you've described so far could be done entirely within Word (e.g. using Building Blocks).

As for Lee's approach, which involves the use of bookmarks, that presupposes your documents will contain bookmarks for every conceivable option, but is inflexible in that, once something has been exported to a bookmark, you can't undo it if you change your mind, other than by editing the document manually.

 

[mmcdonald6]

Thank you for the responses and, I have the same concern with Lee's approach as there could be a lot of bookmarks to manage.
And, you're right to question why I want to have Excel involved because, in the end, I don't really care how I get the results. In fact, a solution entirely within Word is preferred.
This issue arises as a result of an eye opening meeting at work and how uncommon proper use of Word is (mainly with regards to Styles) among my peers. Although the obvious answer is for them to be proficient with a tool they use for hours a day, that is not something within my control.

So, what I really want, is a robust template which allows the user to focus on entering content only while maintaining a consistent look. Building blocks get me close, though I rely on them (users) having abilities to work with Word tables (again, it should be expected but...). Most of the document is tabular (I apologize for the original post being misleading) which look something like this:

Building blocks give me the ability to create a table similar to above, maintaining all the styles and formatting, but requires them to insert and delete rows as needed. Alternatively, I could setup building blocks for each row type and handle it that way.

However, I think this is the method I will take as a stop gap and either source out automation in the future.

Thank you again for your help.

 

[Macropod]

Perhaps, then, the idea would be to start of a template that contains all the possible elements, which the users then use to create a document from which they can delete whatever isn't required. That process might be driven by a userform, in which they check/uncheck various options. Once they've done that, a command button can be used to commit the changes, together with any attendant tidying-up that is a consequence of the options chosen, rather than being options in their own right.