Macro to export cell values to Word

[dn1509]

Dear all,

I am new to this forum so forgive me if this should go in a different forum. Excel is the main or 'host program in what I am trying to achieve so I felt it best fit here.

I have a spreadsheet which is nothing fancy at all - basic columns with manually entered information. I want to be able to highlight one row across 4 columns and have a macro button which will take the information and insert it into certain locations on a pre-existing Word document. I do not need it to save at this point (though this is functionality I may wish to incorporate later).

For example, say columns A, B, C, and D hold my data. I then want to highlight A1 : D1 and click the macro button. This will take the cell values and insert them into the word document as follows:

----------------
Dear A1,

Thank you for your letter dated B1. Your query has been forwarded to our C1 department for further investigation. Should you have any queries please contact your HR representative, D1, who will be happy to assist you.

Regards,

Dominic
----------------

Please note this is a very crude and fundamental example of what I am doing, but there is no need to go into great detail and hopefully this gives you the idea of what I am trying to achieve.

If I need to be clearer on something then please do let me know.

Any help you can offer greatly appreciated!

Regards,

Dominic

[Bob Phillips]

Insert bookmarks in the fieds in the word document that you want to update, then just use Excel to startip Word, open the document, and insert the cell values into the bookmarks.

[lynnnow]

xld, wouldn't mail merge be a better option? I mean if he is aware of the positions where the text goes, the form letter can be modified to suit, and later edited as per needs. I don't dispute bookmarks can be used with Excel being the host application, however, using Word as the host and utilizing mail merge is an easier way to go. Chances are that bookmarks can inadvertently be deleted either by creator or end user and this can throw the Excel macro out of whack. With mail merge, there are fewer chances of that happening. Just wondering if it can be done like this. Sorry if I've questioned your methods.

Also, going by the example provided by Dominic there will have to be a loop that goes through all or some of the records. A mail merge option would allow the flexibility at any time to select which records need to be output and which don't need to be output.

[Bob Phillips]

I suppose maile merge is an option, I must admit I don't like mail merge, it is a clumsy implementation, I prefer to control it. Let' see what the OP comes back with.

[mdmackillop]

I'm sure there are examples of this in the KB.

[Kenneth Hobs]

Try: http://vbaexpress.com/forum/showthread.php?p=185718

[dn1509]

Thank you for your ideas thus far.

Looking through other threads I have managed to throw together this code, using the placeholder [REFNO] in my Word document:

[VBA]Sub automateword()
Dim WordApp As Object
Dim DocWord As Word.Document
Set WordApp = CreateObject("word.Application")
Set DocWord = WordApp.Documents.Open("P:\Folder\File.doc")
WordApp.Visible = True
Set myRange = DocWord.Content
myRange.Find.Execute FindText:="[REFNO]", ReplaceWith:=Cells(9, 1).Text

End Sub[/VBA]

This works great, however I am unable to integrate more find/replaces. I want the following integrated also. Can anyone help me just put these into my code so all 5 placeholders are replaced?

[VBA]myRange.Find.Execute FindText:="[NAME]", ReplaceWith:=Cells(9, 2).Text
myRange.Find.Execute FindText:="[DOB]", ReplaceWith:=Cells(9, 3).Text
myRange.Find.Execute FindText:="[PPNO]", ReplaceWith:=Cells(9, 4).Text
myRange.Find.Execute FindText:="[DATE]", ReplaceWith:=Cells(9, 5).Text[/VBA]

Any help would be much appreciated!

Thanks.

[GTO]

Greetings Dominic,

Not sure how much help I'd be at Word, but maybe use a template. It appears you are using text to find/replace like I used to in WordPerfect.

Anyways, with the template in the same folder as the workbook, try:

Option Explicit
    
Sub exa()
Dim WD As Object '<---Word.Application
Dim DOC As Object '<--- Word.Document
    
    On Error Resume Next
    Set WD = GetObject(, "Word.Application")
    If Err.Number > 0 Then
        Set WD = CreateObject("Word.Application")
    End If
    On Error GoTo 0
    
    With WD
        .Visible = True
        Set DOC = .Documents.Add(Template:=ThisWorkbook.Path & "\MyTemplate.dot", _
                                 NewTemplate:=False, DocumentType:=0)
        With .Selection
            .Find.ClearFormatting
            .Find.Replacement.ClearFormatting
            With .Find
                .Text = "[REFNO]"
                .Replacement.Text = Cells(9, 1).Text
                .Forward = True
                .Wrap = 1
                .Format = False
                .MatchCase = False
                .MatchWholeWord = False
                .MatchWildcards = False
                .MatchSoundsLike = False
                .MatchAllWordForms = False
            End With
            .Find.Execute Replace:=2
            
            .Find.Execute FindText:="[NAME]", ReplaceWith:=Cells(9, 2).Text, Replace:=2
            .Find.Execute FindText:="[DOB]", ReplaceWith:=Cells(9, 3).Text, Replace:=2
            .Find.Execute FindText:="[PPNO]", ReplaceWith:=Cells(9, 4).Text, Replace:=2
            .Find.Execute FindText:="[DATE]", ReplaceWith:=Cells(9, 5).Text, Replace:=2
        End With
    End With
End Sub

Hope that helps,

Mark

[Bob Phillips]

Use bookmarks as I suggested earlier. It is trivial then

[vba]

Doc.Bookmarks("bmk_A1").Range.Text = Activesheet.Range("A1").Value
[/vba]