Sleeper: Merge Excel data to Word document

[UHsoccer]

I have an Excel spreadsheet with codes and related information. I need to use that to prepare a word document. Here is a sample.

Excel data
Sex: Male
Height (inches): 50
Age: 50

Word document template

I am a <sex> and I am <height> inches tall. My age is <Age>.

So the final document should read

I am a male and I am 50 inches tall. My age is 50.

I assume this to become a VB macro in Excel, open word and transfer codes as needed. For starters, I do not know how to create the word template with the codes.

Before completed, I am sure there will be other questions

Thanks

[MWE]

I have an Excel spreadsheet with codes and related information. I need to use that to prepare a word document. Here is a sample.

Excel data
Sex: Male
Height (inches): 50
Age: 50

Word document template

I am a <sex> and I am <height> inches tall. My age is <Age>.

So the final document should read

I am a male and I am 50 inches tall. My age is 50.

I assume this to become a VB macro in Excel, open word and transfer codes as needed. For starters, I do not know how to create the word template with the codes.

Before completed, I am sure there will be other questions

Thanks

You can approach this either way, i.e., VBA code in Word that opens Excel file, ... or VBA in Excel that opens the Word doc. The example that you provided was pretty simple; I suspect that the real problem is more complicated. A few questions:

1. do you have a preference for which appl is the master?
2. how many codes (as you called them) are really involved?
3. if it is important that Word be the final output, why not a Word table. In the simple example you provided, I find the final output much less useful than, say, a table of data.

[Norie]

Can't you just use mail merge in Word with the Excel spreadsheet as a data source?

[UHsoccer]

You can approach this either way, i.e., VBA code in Word that opens Excel file, ... or VBA in Excel that opens the Word doc. The example that you provided was pretty simple; I suspect that the real problem is more complicated. A few questions:

1. do you have a preference for which appl is the master?
2. how many codes (as you called them) are really involved?
3. if it is important that Word be the final output, why not a Word table. In the simple example you provided, I find the final output much less useful than, say, a table of data.

Yes indeed, the problem is quite complicated but I am trying to get the principal issues resolved
The data is being prepared in Excel and from there the word document is to be prepared (and emailed)

There will be about 30-40 codes. It prepares a complete narrative about a person and it has to look and feel like a "professionally prepared document". So the original template is a nicely formatted, indented, bulleted narrative.

If the person is a female then fields are to be adjusted between "his" and "her", etc, etc.

There are areas where a whole paragraph (which comes from a data base) gets inserted

The current code in Excel

1) Open a word document that contains the substitution codes - that works OK

------ Code for opening a document ---------

Dim wordApp As Object
Dim fNameAndPath As String
' fNameAndPath = "c:\test.doc"
fNameAndPath = "C:\Temp\SS-MailMerge.doc"
Set wordApp = CreateObject("Word.Application")
wordApp.Documents.Open (fNameAndPath)
wordApp.Visible = True

2) Do a mail merge. I recorded a mail merge macro and tried to run that.
It responds with "the command is not available, because no document is open"

------- I know the code is ugly-----------
------- Code to do a mail merge ------- It has the error

ActiveDocument.MailMerge.OpenDataSource Name:= _
"c:\test\SS-Merge-2.xls", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=c:\test\SS-Merge-2.xls;Mode=Read;Extended Properties=""HDR=NO;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OL" _
, SQLStatement:="SELECT * FROM `Sheet1$`", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

Reply to Norrie

Yes that can be done, but the idea is to a person answer relevant question in an Excel form, then click a button that says something like "Send document"

A VBA macro finds the answers, formats them consistent with a mail-merge ready document, fills in the substitution fields and email the document.

I am trying to validate the concept

[andy_uk]

Hi UHsoccer. I don't know if I'm allowed to say this in here ... I wouldn't use Word (ie: an extra program) at all ; & I'd avoid using VBA if possible.

Although it may not fit what *you're* doing, my current client is more than happy with the following method:

1 - the person answers the questions in one "Excel form" sheet.

2 - the answers are picked up by formulas on a second sheet, which is laid out as a document.

3 - the person prints the second sheet using CutePDF (free download, search Google), which converts it to a PDF, which is then sent.

Laying out a second sheet so it looks and feels like a "professionally prepared document" is very possible, believe me. Can you post your Word template?

Rgds,
Andy